Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hoho1
Frequent Visitor

Measure should stay fixed

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:

 

BadsCount
01200
1100
Total1300

 

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:

BadsCount% of OriginalCalculation for % of Original
060050%=600/1200
12020%=20/100
Total62048%=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!

1 ACCEPTED SOLUTION
vmakhija
Post Prodigy
Post Prodigy

@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

View solution in original post

4 REPLIES 4
vmakhija
Post Prodigy
Post Prodigy

@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

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

 

anandav
Skilled Sharer
Skilled Sharer

Can you try:

 

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

Hoho1
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.