Percentile.exc Error

[gooranga1]

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?

[v-jiascu-msft]

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,

[gooranga1]

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.

[OwenAuger · 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

[gooranga1]

Thanks for the explanation @OwenAuger 

[gooranga1]

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.

[pacoPBI]

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

[pacoPBI]

thanks