cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
atulj10 Regular Visitor
Regular Visitor

DAX- Percentage calculation at different granularity

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.

 

DaxQues.png

 

Thanks,

Atul

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: DAX- Percentage calculation at different granularity

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
OwenAuger Super Contributor
Super Contributor

Re: DAX- Percentage calculation at different granularity

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

mochabits Regular Visitor
Regular Visitor

Re: DAX- Percentage calculation at different granularity

@atulj10 

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)
atulj10 Regular Visitor
Regular Visitor

Re: DAX- Percentage calculation at different granularity

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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)