Remove 1 from a sum to get correct percentage

[Saarek]

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.

[Ashish_Mathur · Accepted solution]

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 %.

[Saarek]

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

[Ashish_Mathur]

You are welcome.

[VijayP]

@Saarek 

If You can create two different views?!

[Saarek]

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]

@Saarek 

Try using ISFILTERED

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

it should give you the right view.

[Saarek]

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.