Calculate percentile with filters

[ilhamfadhillah4]

Hi everyone,

So I have a problem with calculating percentiles filtered based on the value of another column, here's an example:

The data that I have is in the left table, I want to add the 'Percentile' column in the same table containing the percentile value of the total budget in the account for that row, so the end result should be like the one in the right. My current formula is as follows:

PERCENTILE = PERCENTILE.EXC(Table[Budget],0.8)

Which returns overall percentile of the data instead of it being the percentile of each account. I tried the following formula as well:

PERCENTILE = CALCULATE(PERCENTILE.EXC(Table[Budget],0.8),Table[Account])

 Which returns an error because the filter expression returns a string value and cannot be converted into a true/false.

How do I make sure that the percentile value in each row is specific to the percentile value for the account in that row? So any row with the same account will return the same value. Thanks in advance!

[ilhamfadhillah4]

I just tried that and it works, thank you so much!

[lbendlin · Accepted solution]

PERCENTILE = CALCULATE(PERCENTILE.EXC(Table[Budget],0.8),ALLEXCEPT(Table,Table[Account]))

this would be one option, but it's a rather blunt instrument. You may need to use different filter context manipulations.