Calculate percentile with filters

ilhamfadhillah4 — Thu, 16 Mar 2023 06:50:32 GMT

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!

Re: Calculate percentile with filters

lbendlin — Sun, 19 Mar 2023 20:29:43 GMT

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.

Re: Calculate percentile with filters

ilhamfadhillah4 — Mon, 20 Mar 2023 03:33:06 GMT

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

topic Re: Calculate percentile with filters in DAX Commands and Tips

Calculate percentile with filters

Re: Calculate percentile with filters

Re: Calculate percentile with filters