cancel
Showing results 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

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
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
7 REPLIES 7
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
Helper III

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

If You can create two different views?!

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Helper III

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.

Super User

Try using ISFILTERED

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

it should give you the right view.

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Helper III

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.