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

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

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.

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.

Hi @lynn, we’ve noticed that no response has been received from you since August 17.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.