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.
Is it possible to calculate a percentage difference that dynamically changes based on slicers.
Table 1: YTD
Table 2: What I want when filtering with slicers (here by month)
Table 3: The result
I've tried two methods to show the Mix % calculation:
1) show mix % based on volume column as percentage of column - but can't calculate correct mix difference
2) calculate mix by dividing volume by sum of volume - doesn't work because the sum of volume is based on a different summary table and therefore can not filter correctly with slicers (I need to be able to filter my table and show 100% mix based on what is filtered and not over the original total)
Any suggestions?
Solved! Go to Solution.
Hi @CD1
You may try to use ALLSELECTED Function (DAX) as below. Here is the reference for you.
https://community.powerbi.com/t5/Desktop/Percentage-Sum-Total/m-p/469771#M218237
Mix% Actual = DIVIDE ( SUM ( 'table'[Volume] ), CALCULATE ( SUM ( 'table'[Volume] ), ALLSELECTED ( 'table'[Slicer] ) ) )
Regards,
Cherie
Hi @CD1
You may try to use ALLSELECTED Function (DAX) as below. Here is the reference for you.
https://community.powerbi.com/t5/Desktop/Percentage-Sum-Total/m-p/469771#M218237
Mix% Actual = DIVIDE ( SUM ( 'table'[Volume] ), CALCULATE ( SUM ( 'table'[Volume] ), ALLSELECTED ( 'table'[Slicer] ) ) )
Regards,
Cherie
Can you share what you have tried and the actual and desired results. (i.e. what do you measures look like, if you cant' share you model at least show a screen shot of the tables and hwo they are related and the definitions of the key measures.
Tip - the measures will probably look like this
Total Volume Actual = CALCULATE([Volume_Act],All(table[Company]))
Mix % Actual = DIVIDE([Volume_Act],[Total Volume Actual])
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |