Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gooranga1
Power Participant
Power Participant

Percentile.exc Error

I have a calculation;

 

 

Percentile 95 = if(ISERROR(PERCENTILE.exc(FactDoorOpenTimes[SecondsOpen],0.95)),BLANK(),PERCENTILE.exc(FactDoorOpenTimes[SecondsOpen],0.95))

That calculates the 95 percentile correctly. However I also have some slicers that can filter the data and when I select some of them I get the following error.

 

Percentile1.PNG

 

 

Percentile.PNG

 

I think it's failing when there is not enough data and I have tried to capture the error and make it fail in a more elegant way than a big x error message but no joy. 

 

Anyone with any idea how I can stop it erroring when I don't have enough data to calculate the appropriate percentile?

1 ACCEPTED SOLUTION

@gooranga1 

 

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.

 

Instead, you can test whether the percentile rank (k = 0.95 in your case) is within the allowable range before calling the PERCENTILE.EXC function, which avoids PERCENTILE.EXC being calculated when not valid.

 

This measure should do the trick (a few variables used for clarity):

 

Percentile 95 a =
VAR k = 0.95
// Both COUNT and PERCENTILEX.EXC ignore blanks
VAR NumValues = COUNT ( FactDoorOpenTimes[SecondsOpen] ) VAR LowerBound = 1 / ( NumValues + 1 ) VAR UpperBound = NumValues / ( NumValues + 1 ) RETURN IF ( AND ( k >= LowerBound, k <= UpperBound ), PERCENTILE.EXC ( FactDoorOpenTimes[SecondsOpen], k) )

Regards,

 

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @gooranga1 ,

 

It seems you have tried to capture the error. Can you share a sample? Please mask the sensitive parts. 

Maybe you can try iferror-function-dax.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-jiascu-msft 

 

I tried your suggestion using iferror but got the same errors.

 

https://1drv.ms/f/s!AtmbigV0K02yw1jQk7txTW_Gxyjn

 

Above is the link to a folder with a pbix called ErrorPercentile that has an anonomised version of the report that I can re-produce the error.

 

To do this select any other category or any group name in the slicers.

 

ErrorPercentile.PNG

@gooranga1 

 

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.

 

Instead, you can test whether the percentile rank (k = 0.95 in your case) is within the allowable range before calling the PERCENTILE.EXC function, which avoids PERCENTILE.EXC being calculated when not valid.

 

This measure should do the trick (a few variables used for clarity):

 

Percentile 95 a =
VAR k = 0.95
// Both COUNT and PERCENTILEX.EXC ignore blanks
VAR NumValues = COUNT ( FactDoorOpenTimes[SecondsOpen] ) VAR LowerBound = 1 / ( NumValues + 1 ) VAR UpperBound = NumValues / ( NumValues + 1 ) RETURN IF ( AND ( k >= LowerBound, k <= UpperBound ), PERCENTILE.EXC ( FactDoorOpenTimes[SecondsOpen], k) )

Regards,

 

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks for the explanation @OwenAuger 

So I fixed by chnaging the formula to;

 

Percentile 95 a = IFERROR(PERCENTILE.inc(FactDoorOpenTimes[SecondsOpen],0.95),BLANK())

So changing it from inclusive to exclusive. Unfortunately my limited knowledge means I can't explain exactly why.

Hello this works ok in Excel, but don't works in powerbi

 

pag1.pngpag2.png

thanks 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.