Percentile Calculation Broken

[v-sheset]

Hi,

This is not the first time I am working with Percentile calculations in PBI. However within a report Percentile calculations seem to be working odd. The numbers are incorrect and also the formula breaks when trying to calculate the 99th percentile.

To test the numbers I did percentile calculations on the desired field in Kusto and the numbers seem correct, however they do not match with prcentile calculations when done in Power BI by creating a measure. I am not sure what am I doing wrong.

Attached visual that is unsupported is when attempting to calculate the 99th percentile

[v-haibl-msft]

@v-sheset

Do you mean that the measure which used to do percentile calculation does not return the correct result in Power BI Desktop? If yes, could you please provide the measure formula and some sample data?

Best Regards,
Herbert

[v-sheset]

Yes, That is what I mean. Percentile Calculation does not return the correct value in Power BI Desktop.

Formula used:

Percentile95DECancellation = PERCENTILE.EXC('DE_FDHE_Cancellation_Time'[timeToCancelDE],0.95)

Percentile98DECancellation = PERCENTILE.EXC('DE_FDHE_Cancellation_Time'[timeToCancelDE], 0.98)

Percentile99DECancellation = PERCENTILE.EXC('DE_FDHE_Cancellation_Time'[timeToCancelDE], 0.99)

I have the sample data, Dont know how to attach it here. 😞 Some values are as below for example

31.01:09:47.0103292

16.05:24:17.5955781

13.02:39:28.4676111

7.14:21:18.0016487

6.02:09:30.6411037

5.22:42:12.2269722

5.21:35:13.7168207

5.17:50:06.5552803

5.14:25:02.5225867

5.00:24:06.9244826

4.11:55:13.5877447

4.03:50:18.6386796

3.23:06:15.8808008

3.22:08:25.4451047

3.17:55:23.4778271

3.06:34:19.6658091

3.03:12:20.9459942

3.00:55:37.6928401

2.23:24:03.3628356

2.23:21:16.0764442

2.12:59:38.0208098

[GenoRock]

I too am having problems with the Percentile.exc function.

I have a very simple function that relies on an int field (eNotifyAge) sourced from SQL Server 2008r2.

eP90 = PERCENTILE.EXC(eDwEventData[eNotifyAge],.9)

The value returned appears to be fine within my List or Matrix object at the aggregate level. But when I apply various filters (looks like filters that return no data), I get the following error. 

"This Visual has an unsupported percentile."

The problem is even more significant when I have a row field in my Table or Matrix. I get summary numbers including my eP90 field with calculated values but when I apply a filter that would make any one of my row values be essentially zero records, I get the unsupported percentile error.  Clearly, I should be able to have a visualization that returns all summary data for the relevant filter and not be so tempermental. 

Please advise on what the magical tricks are on percentile - Excel works sooo well with percentiles --- except the fact that it isn't available in Pivot summaries - ugh.

[v-haibl-msft]

@v-sheset

With the sample data you provided, I also get error in Excel. Could you please try the same data in Excel on your side?

Best Regards,
Herbert

[destam]

i think this will show up if you don't have enough data points for the specific cut in order to calculate the percentile.

i have the same problems, but still don't know how to resolve that.

have tried if(iserror()) type of formula, but didn't work...

[v-sheset]

 @v-haibl-msft: I agree I tried this in excel but it didnt work. I tried the same thing in Kusto it works fine with the data. Also there are others where the percentile was working fine with no issues and now they have broken too with the same error message of unsupported visual. Not sure how to fix this. 

[KingKong333]

Here is the workaround I used:

= if(count([XXX])>99,PERCENTILEX.EXC(Query, [XXX],0.98),"")

This will show Blank if there are less then 100 items in the list, instead of throwing error.

[Anonymous]

I had the same problem and while @KingKong333 your solution works, it creates a lot of problems with filtering/shaping your data, because you're mixing text and numeric values. Here's a simple modification that prevents this:

= if(count(tableName[columnName])>99,PERCENTILEX.EXC(Query, tableName[columnName],0.98),BLANK())

or if you just want simple percentiles without a query:

= if(count(tableName[columnName])>99,PERCENTILE.EXC(tableName[columnName],0.98),BLANK())

The condition in the if statement should check whether count is large enough to calculate the percentile you want. E.g. if you want 90th percentile, it needs to be >9, whereas for 75th it would be >3, etc.