# Power BI Percentile.Exc

percentileexc.pbix (20.0 KB)
sample data1.xlsx (12.6 KB)
Hello Everyone!

I need to create a peer benchmark visualizations that shows the 1st Quartile 2nd Quartile and 3rd Quartile using the percentile.exc. The main purpose is to see where the company (peers) is in the quartile. As you can see in the card visualization I’m using the percentile.exc calculation and the no. is correct. However when I use scatter plot and use the Analytics Percentile line the no. is not correct. I think it uses the percentile.inc in the calculation. Maybe you can help me solve this problem or if you have better ideas to do this. Thanks

sample data1.xlsx (12.3 KB)

hi @lynn

There are two way to use percentile on Power BI. Update your DAX to Percentile.INC to match with the chart percentile

`1st Quartile = PERCENTILE.INC('data (2)'[O&M per Customer],.25)`

Hello, Thanks for the answer. However we needed to have the percentile.exc. Is there a way we can use the percentile.exc?

Hi @lynn

percentileexc-jb.pbix (21.0 KB)

2 Likes

Awesome! Thank you for taking time to answer this.
One more thing when i tried to filter the peers the quartile is not changing.

In the screenshot once the Peers filter is selected it should also calculate the percentile for the selected.

1 Like

Hi @lynn

``````1st Quartile =
VAR k = 0.25
// Both COUNT and PERCENTILEX.EXC ignore blanks
VAR NumValues =
COUNT ( 'data (2)'[O&M per Customer])
VAR LowerBound = 1 / ( NumValues + 1 )
VAR UpperBound = NumValues / ( NumValues + 1 )
RETURN
IF (
AND ( k >= LowerBound, k <= UpperBound ),
CALCULATE(PERCENTILE.EXC('data (2)'[O&M per Customer],k),ALLSELECTED('data (2)'[Peers])),0
)
``````

K = Percentile, for 2nd Quartile change to 0.50 and 0.75 for 3er Quantile

2 Likes

Thank you!!!
However it’s not working on the graph.
And the table is not getting the result like the 2nd quartile.

percentileexc-jb.pbix (22.2 KB)

Hi @lynn

If you have n values, PERCENTILE.EXC treats the minimum value as having percentile rank 1/(n+1) and the maximum value as having percentile rank n/(n+1). For example, if you have 9 values, the lowest allowable percentile rank is 1/(9+1)=0.1 and the highest is 9/(9+1)=0.9.

PERCENTILE.INC, on the other hand, treats the minimum value as having percentile rank 0 and the maximum value has having percentile rank 1.

I see from your reply that you’ve switched to PERCENTILE.INC, which can handle any percentile rank value from 0 to 1.

If you did want to stick with PERCENTILE.EXC, you can do this but unfortunately IFERROR or IF ( ISERROR (…) ) aren’t good enough to safeguard against the error in this case. It appears Power BI can’t handle evaluating an invalid percentile even if it is simply used to test for an error value.

To Response your question the last measure that I provide to you is to handled on chart the Dynamic percentile on base of your filter selection. I changed from ALL to ALLSELECTED.
When the measure return 0 is because I try to avoid the error message.

2 Likes

Thank you!
I wanted to stick to percentile.exc.
However the card showing the correct percentile but not in the graph.
As you can see the 1st and 3rd quartile showing 0.

Hello all, bumping this post for more visibility.

Is there any alternative solution to this? Badly needed. Thanks

Hi @lynn,

Enable “Show secondary” then Enable “Align zeros” and disable “Show secondary” again

Next create a display measure

``````1st Q to diplay =
VAR vTable = ALLSELECTED( 'data (2)' )
RETURN
CALCULATE(
CALCULATE( [1st Quartile],
vTable
),
REMOVEFILTERS( 'data (2)' )
)
``````

Place those in the Line and stacked column chart

percentileexc-jb.pbix (23.7 KB)

2 Likes

Hi @lynn good to see that you are having progress with your inquiry.