Forum Discussion
90th Percentile Issues (Not displaying correctly)
I am having issues with capturing the 90th percentile of a given set of data. I have used all the percentile forumals below with no luck. While it returns results, they seem to be incorrect.
- PERCENTILE.EXC('Public-Dashboard'[Unit Turnout Time],0.9)
- PERCENTILE.INC('Public-Dashboard'[Unit Turnout Time],0.9)
- PERCENTILEX.EXC('Public-Dashboard',[Unit Turnout Time], 0.9)
- PERCENTILEX.INC('Public-Dashboard',[Unit Turnout Time],0.9)
Below is a certain set of data where you can see the results of the forumla are returning incorrect results as I did the calculation manually on paper. As you know a normal 90th percentile formula would be calculated as the following steps.
- Put data in chronological order
- Count how many data points you have (in the example below it would be 23)
- Take 23 * 0.9 which returns 20.7
- Your 90th percent would be the 21st number or 20th number.
Based on the images below I would assume it would be 110 or 118.
Any help would be appreciated.
Hi CPage-WF ,
You can try to write the result you want in Dax, i have created an example below:PERCENTILE.EXC = VAR _1 = ISO.CEILING( COUNTAX ( 'Public-Dashboard', 'Public-Dashboard'[Index] ) * 0.9, 1 ) RETURN CALCULATE ( SUM ( 'Public-Dashboard'[Unit Turnout Time] ), 'Public-Dashboard'[Index] = _1 )Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi CPage-WF ,
Based on the information you provided, the function returns an incorrect result because it returns a value with a decimal instead of the 21st or 20th digit.
You can use the ROUND function to round the result of the PERCENTILE.EXC function to an integer. This way, you get an integer result.PERCENTILE.EXC = ROUND ( PERCENTILE.EXC ( 'Public-Dashboard'[Unit Turnout Time], 0.9 ), 0 )Or you can write your own dax to calculate the result:
Measure = VAR _1 = ROUND ( COUNTAX ( 'Public-Dashboard', 'Public-Dashboard'[Index] ) * 0.9, 0 ) RETURN CALCULATE ( SUM ( 'Public-Dashboard'[Unit Turnout Time] ), 'Public-Dashboard'[Index] = _1 )Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous - While I was very hopeful that this would work, it seems to still return the wrong value. See below, in this specific data set there are a total of 19 calls so 19 * 0.9 = 17.1 so it should show the 18th value at 77 but instead it is showing 75. I am not sure where it is getting this odd value from.
I am not sure how to explain the data that is in the able. See below as each unit has a different turnout time for each incident
Hi CPage-WF ,
You can try to write the result you want in Dax, i have created an example below:PERCENTILE.EXC = VAR _1 = ISO.CEILING( COUNTAX ( 'Public-Dashboard', 'Public-Dashboard'[Index] ) * 0.9, 1 ) RETURN CALCULATE ( SUM ( 'Public-Dashboard'[Unit Turnout Time] ), 'Public-Dashboard'[Index] = _1 )Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
