Power BI Percentile.Exc

Power BI
1

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)

2

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)

image
image1710×927 53.7 KB

3

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

4

Hi @lynn

image
image1691×910 50.2 KB

Please check the file

percentileexc-jb.pbix (21.0 KB)

2 Likes
5

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.

percentile
percentile1386×796 38.9 KB

1 Like
6

Hi @lynn

Please update your measure with this DAX code

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
7

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

percentiletable
percentiletable1407×445 22.8 KB

percentileexc-jb.pbix (22.2 KB)

percentile
percentile1897×821 89.1 KB

8

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.

image
image839×301 12.8 KB

2 Likes
9

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.

percentileexc
percentileexc1460×821 45.8 KB

10

Hello all, bumping this post for more visibility.

11

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

12

Hi @lynn,

Give this a go. First adjust your visuals Y axis settings:
Enable “Show secondary” then Enable “Align zeros” and disable “Show secondary” again
image

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

image
image1342×815 39.2 KB

Here’s your file.
percentileexc-jb.pbix (23.7 KB)

I hope this is helpful.

2 Likes
13

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

Did the response provided by @Melissa help you?

Kindly mark as solution the answer that solved your query. Thanks!

14

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.

15

Thanks. I still need to validate it now.

16

It Seems working!!! Awesome! Thank you for your help.
@jbressan Thank you!