Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |