95th Percent Value Not 95% of the value

[Anonymous]

Hi,

I have been trying to get this to work but I simply can't. What I am after is the 95th percent value. I have a table named events and the information in it is just like this. 

If i use the percentile.inc dax formula I  will get the value 31.35. What I want is the 95th% row value in this case the value would be 25. Calculated by ordering the values in order, then calculating the row at 95% (10*0.95) which is 10.45. The 10th row value is 25.

Anybody got any ideas how this can be done?

Panel	Grading	Completed Time
57220	DP4	8
57220	DP4	25
57220	DP4	22
57220	DP4	33
57220	DP4	15
57220	DP4	16
57220	DP4	19
57220	DP4	20
57220	DP4	23
57220	DP4	5
57220	DP4	10

Thanks in advance

[TheoC · Accepted solution]

Hi @Anonymous 

Okay, so a few quick steps should help you get to the outcome you're after. The approach I took isn't the best but it's hard to confirm the data types, etc., of each PBIX and all that.  So, to ensure you get what you are after, I created three Calculated Columns in the following order:

1_Rank = RANKX ( 'Table' , Table[Completed Time] , , ASC )

2_Percentile = INT ( COUNTROWS ( 'Table' ) * 0.95 )

3_Match = IF ( Table[1_Rank] = 'Table'[2_Percentile] , Table[Completed Time] , 0 )

The output is pretty on point 🙂

I hope this is what you were after?  

All the best,

Theo

[Anonymous]

Thanks @TheoC  this really helped and worked initially. When I added more panels and timings I had to make some minor changes and I got it working

[TheoC]

@Anonymous great to hear! And well done on the adjustments!