Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Is there any way to create a measure that holds two values that stay fixed when you select other filters?
I have the following table:
Bads | Count |
0 | 1200 |
1 | 100 |
Total | 1300 |
and when I use a filtered column (e.g. Income drops: Y or N) I want to see the new distribution of counts but the % reduction on the original volume, i.e. this:
Bads | Count | % of Original | Calculation for % of Original |
0 | 600 | 50% | =600/1200 |
1 | 20 | 20% | =20/100 |
Total | 620 | 48% | =620/1300 |
Is there any way to do this? I've tried everything I found online (that I could understand) about measures, AllSelect etc but the denominator always changes when I use a filter (I'm fairly new to PowerBI). I used the advanced editor to get a fixed denominator for Bads=1 but obviously it won't work for Bads=0. I'd prefer to do this in DAX but any solution that can help is much appreciated! 🙂
Thank you in advance for your help!
Solved! Go to Solution.
I am assuming you have separate columns for numerator and denominator.
If so, you can use the following -
Original % Measure = IF(HASONEVALUE(Table5[bad]),
CALCULATE(SUM(Table5[Count1]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count2]), ALLEXCEPT(Table5, Table5[bad])),
CALCULATE(SUM(Table5[Count1]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count2]), ALL(Table5))
)
Mainly, HASONEVALUE will treat your individual rows v/s. Total row differently. Hope it helps.
Regards
I am assuming you have separate columns for numerator and denominator.
If so, you can use the following -
Original % Measure = IF(HASONEVALUE(Table5[bad]),
CALCULATE(SUM(Table5[Count1]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count2]), ALLEXCEPT(Table5, Table5[bad])),
CALCULATE(SUM(Table5[Count1]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count2]), ALL(Table5))
)
Mainly, HASONEVALUE will treat your individual rows v/s. Total row differently. Hope it helps.
Regards
A million thanks @vmakhija! Your solution did exactly what I wanted! I can't thank you enough!
Can you try:
Original % Measure = CALCULATE(SUM(Table5[Count]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count]), ALL(Table5))
Thanks for your response @anandav however your denominator gets fixed to (based on my example) 1300 which is not quite what I want. I need it to be equal to 1200 when Bads=0 and equal to 100 when Bads=1. It's on the right path though! 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |