Forum Discussion
Percentile.exc Error
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
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
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
