cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Impactful Individual
Impactful Individual

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!

Connect on Twitter
Connect on LinkedIn

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

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!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Impactful Individual
Impactful Individual

Thanks for the explanation @OwenAuger 

Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors