DAX- Percentage calculation at different granularity

[Anonymous]

Hi all,

I have a table that looks like below. And the screenshot also shows what I am trying to do. I am trying to get a percentage of a column based on the slicer selected.
The one in green is the incorrect operation.

The one in orange is what I need.
My numerator needs to be the row context, and denominator slicer (filter) context. I know using a ALL will select the whole column, but I don't want that. I want my denominator to be only the values that are selected in the slicer.

Thanks,

Atul

[Anonymous]

@Anonymous 

ratioColumn =

 DIVIDE(

CALCULATE(SUM(Products[Prior volume]), FILTER(ALL(Products),

Products[Prod lvl 1] = EARLIER(Products[Prod lvl 1]) &&

Products[Prod lvl 2] = EARLIER(Products[Prod lvl 2]))),

CALCULATE(SUM(Products[Prior volume]), FILTER(ALL(Products), Products[Prod lvl 1] = EARLIER(Products[Prod lvl 1]))),

0)

[OwenAuger · Accepted solution]

Hi Atul,

You can use ALLSELECTED instead of ALL to do this.

To make your measure as general as possible (based on your current model), you could write

P Mix = 
DIVIDE (
    SUM ( Sheet1[Prior Volume] ),
    CALCULATE ( 
        SUM ( Sheet1[Prior Volume] ),
        ALLSELECTED ( Sheet1[Product Hier], Sheet1[Product Hier lvl 1], Sheet1[Product Hier lvl 2] )
    )
)

Using this measure, the denominator would be determined by any slicers filtering the table.

For convenience, it might be better to have a Product table related to your existing table, which would simplify the above measure since you could write ALLSELECTED ( 'Product' ).

Regards,

Owen

[Anonymous]

Owen,

Thank you.

I tried using ALLSELECTED too, but only with Prod Heir Level 1, which gave me 100% every time. this makes sense now. Thanks again.

Regards,

Atul