Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
ratioColumn =
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |