Forum Discussion
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.
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?
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
7 Replies
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,
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.
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
