DAX how to Ignore certain slicers in measure?

0
votes

I have a table like below:

BU  Value   Date    Measure Agg_Lvl_1   Agg_Lvl_2   Agg_Lvl_3
AA  10  01/01/2021  Sale    Firm    COO A
AB  20  01/04/2021  Sale    Firm    Non-COO A
AC  32  01/05/2021  Sale    Firm    COO A
BA  32  01/01/2021  Sale    Firm    Non-COO B
BB  43  01/04/2021  Sale    Firm    Non-COO B
BC  19  01/08/2021  Sale    Firm    Non-COO B
CA  11  01/11/2021  Sale    Firm    Non-COO C
CB  16  01/12/2021  Sale    Firm    Non-COO C
CC  18  01/13/2021  Sale    Firm    COO C
D   18  01/01/2021  Sale    Ext Non-CIO D
AA  10  01/01/2021  non-Sale    Ext Non-CIO A
AB  20  01/04/2021  non-Sale    Firm    Non-CIO A

I need to calculate each BU's contribution for Firm Sale by period: contribution = Sum(Table(Value) where Measure ='Sale' & BU ='slicer select') / Sum(Table(Value) where Measure ='Sale' & BU ='Firm' also this "contribution" measure should correspond to date slicer

I have tried different DAX method all i got was contribution of 1 (i think the slicer/filter isn't set up right). Anyone please help?

E.g. AA contribution between 1/1/2021 - 1/4/2021 = (10+20)/ (10+20+32+18) = 12.5%

1
powerbidax

1 Answers

1
votes

You can try the function ALLEXCEPT as shown below-

contribution = 
CALCULATE(
    SUM(Table_name[value]),
    FILTER(
        ALLEXCEPT(Table_name,Table_name[Date],Table_name[BU]),
        Table_name[measure] = "Sale"
    )
)
/
CALCULATE(
    SUM(Table_name[value]),
    FILTER(
        ALLEXCEPT(Table_name,Table_name[Date]),
        Table_name[measure] = "Sale"
            && Table_name[BU] = "Firm"
    )
)