Measure should stay fixed

[Hoho1]

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!

[vmakhija · Accepted solution]

@Hoho1

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

[Hoho1]

A million thanks @vmakhija! Your solution did exactly what I wanted! I can't thank you enough! 

[anandav]

Can you try:

Original % Measure = CALCULATE(SUM(Table5[Count]), ALLEXCEPT(Table5, Table5[bad])) / CALCULATE(SUM(Table5[Count]), ALL(Table5))

[Hoho1]

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! 🙂