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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Saarek
Helper III
Helper III

Remove 1 from a sum to get correct percentage

I have an expression that works perfectly until I apply a filter:

Plan_Rate = sum(Tiering[Retained_AP_ZRC]) / sum(Tiering[Retained_AP]) -1
 
The issue with the formula and applied filters is the -1 at end of the expression.
 
The -1 turns an amount of 108.4% to 8.4%, when I apply a filter on say the date range, the correct values show for all entries that fall within that date and then the rest show up as -100%, but they should not show at all because they do not fall within the date range.

I'm still relatively new to Power BI and this is a head hit keyboard moment for me, I can't find any variation of sum that works the way I need.
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

Plan_Rate = divide((sum(Tiering[Retained_AP_ZRC])-sum(Tiering[Retained_AP])),sum(Tiering[Retained_AP]))

Format this measure as a %.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

Plan_Rate = divide((sum(Tiering[Retained_AP_ZRC])-sum(Tiering[Retained_AP])),sum(Tiering[Retained_AP]))

Format this measure as a %.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes, that works perfectly. Thank you very much for your help!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VijayP
Super User
Super User

@Saarek 

If You can create two different views?!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


It's a report, the way it is currently setup the users choose filters. Someone brought to my attention that when they apply a date filter and then scroll down they can see entries that should not reflect, although the totals are fine.

My head says there must be a way within Power BI to apply a percentage filter correctly. The method I used is one I use in Excel and R, but I appreciate that Power BI uses Dax.

But yes, creating two pivot tables to correct something that should not show on the pivot table in the first place won't do in this example.

VijayP
Super User
Super User

@Saarek 

Try using ISFILTERED

IF(ISFILTERED(Filter),Plan_Rate  wihtout "-1", Plan_Rate with "-1")

it should give you the right view.




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Hi @VijayP , Thank you for your suggestion. Am looking up your syntax suggestion now, it's new to me. From what I can gather if a filter is applied then show plan rate without -1, but if a filter is not applied then show the -1.

Only problem I have is that users will want to be able to view the same pivot table in a filtered and unfiltered form.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.