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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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