Really dumb percentile question

[masplin]

I'm struggling to understand the percentile calcuation given what I believe is the mathematical definaition of percentile. Can someone put me straight. 

I created this table of 10 values

a	0
b	1
c	2
d	3
e	4
f	5
g	6
h	7
i	8
j	9

Accroding ot Wikipedia and my schol maths the 20th percentile is that value where 20% of the obvservations are at or below. So to me that should be at the value of 1 as 1 amd 0 are at or below which is 2 out of 10.

however this measure generates a result of 1.8

Measure = PERCENTILE.INC('Table'[Column2],0.20)

Using 0.5 gives 4.5 which maybe makes sense as 5 values are below and 5 values above. 

The interpolation is obviously doing somethnig clevered than i can understand, but trying to explain the result to my client is a bit of a problem!!!!

If someone can give me an idiot explanation of why 20th percentile is 1.8 I'd appreciate it

Mike

[Greg_Deckler]

I think this is a pretty good description:
https://exceljet.net/excel-functions/excel-percentile.inc-function

I realize it is Excel, but they function the same

[masplin]

Thanks for that, but the result still dont make any sense.  If you take the first quartile which comes out at a vlaue of 3.25. Looking at values 1 to 10 I see 3 values are less than 3.25, 1,2 & 3 so that 3 out of 10 values in the list which is how you define the 30th percentile? How can that be the 25th percentile. 

If you have an infinite list of values then percentiles are very simple. If you have 1000 values the 25th percentile is count up 251 values from the bottom to the point where 25% of values are below.  Something completely different is happening with this interpolation method that is illogical to my mind.   

[masplin]

It's something to do with the bottom number being 0th percentile. So between 1 and 10 have fit 100 perectilesis so each 10th is 0.9 starting form 1 so 10th is 1+0.9.  However seems to create a wierd answer for 25th with 30% of values lower than it!!!  

	percentile	Value
Values	0	1
1	0.1	1.9
2	0.2	2.8
3	0.3	3.7
4	0.4	4.6
5	0.5	5.5
6	0.6	6.4
7	0.7	7.3
8	0.8	8.2
9	0.9	9.1
10	1	10

I guess the PERCENTILE.EXC function makes more sense as 25th percentile is 2.75 

[v-lionel-msft]

Hi @masplin ,

PERCENTILE.INC(array，k):

Sort the array from small to large (ascending order), let n be the number of array elements, and calculate the integer part of k * (n-1) as a and the decimal part as b.
Then the value of PERCENTILE.INC (array, k) : The (a + 1) th value of the array * (1-b) + The (a + 2) th value of the array*b

Measure = PERCENTILE.INC(Sheet1[Index], 1)

Measure 2 = 
CALCULATE(
    PERCENTILE.INC(Sheet1[Index], 1),
    ALL(Sheet1)
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[masplin]

Sorry that made it no clearer.

[v-lionel-msft · Accepted solution]

Hi @masplin ,

I have mentioned the calculation logic of the PERCENTILE.INC() function before, and I have done the corresponding calculation below.

n = COUNT(Sheet2[array])

k * (n-1) = 0.1 * ([n] -1)

//a is integer part of k*(n-1)
a = 0

// b is decimal part of k*(n-1)
b = 0.9

the value of PERCENTILE.INC (array, k) = 1 * (1-0.9) + 2 * 0.9

Measure = PERCENTILE.INC(Sheet2[array], 0.1)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.