How to calculate percentiles using Averaged Empirical rule, in the way SPSS CTABLES do, in Power BI

[Bemused]

Hi everyone 🙂

So I've been struggling with this a lot, and searched in Google and on the Community forums, can't find the solution anywhere.

Please bear with me, my quantitative skills aren't hugely amazing!

I have a column of values as below (dummy data):

[ID]	[Values]
1	0
2	6
3	0
4	4
5	2
6	5
7	0
8	5
9	1

I need to calculate the 95th percentile for [Values], inclusive of all numbers, but I need to do so using the same way that SPSS does with CTABLES--using the averaged empirical (AEMPIRICAL) method. This issue is detailed here:  https://www.ibm.com/support/pages/how-does-spss-statistics-calculate-percentiles-frequencies-or-ctab...

And I think the specific method is this one: https://www.xycoon.com/method_4.htm 

Using PERCENTILE.INC does not achieve this for some of the values; e.g. where Power BI says the percentile is 0.4, SPSS says 1.

So I guess my broad question is: how do you calculate the 95th percentile in Power BI in the same way as SPSS using CTABLES?

Any help would be brilliant!

[Bemused]

I don't suppose anyone else has any ideas...?

[amitchandak]

@Bemused , Not very clear on the method shared. Please see if these can help a bit

https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/

[Bemused]

Yeah, it's a bit hard to explain without having delved deep into the world of percentiles!  I didn't even realise there was more than one way to calculate percentiles--there's eight!

From what I understand of the links you provided, yeah it's heading in the right direction.  I've tried splitting the AEMPIRICAL formula up into distinct steps and putting it into DAX, but I'm still having some major problems with RANKX and CALCULATE--and this is probably more my ignorance than any problem with Power BI.

The search continues, I just hope someone more adept at it out there has encountered this issue.  Meanwhile I'll keep plugging away!