PERCENTILE.INC & PERCENTILE.EXC does not yield the desired value

[Nigel99]

Name	Marks
A	10
A	20
A	30
A	40
A	50
A	60

PERCENTILE.INC(Table[Marks],0.25) =22.5
PERCENTILE.EXC(Table[Marks],0.25) =17.5

I would like to determine Q1 from dataset. Q1 should be 20.
Why do the results produced by DAX above not equivalent to 20?

[Ashish_Mathur]

Hi,

Even in MS Excel, the answer is 22.5.  So that answer seems correct.

[Nigel99]

Excel and Power BI share same function. Needless to say, both yield the same output.

If we were going to determine Q1 using conventional method, we will be sorting the group of data in ascending order (in this case, 10,20,30,40,50,60).

Q1 is deemed the half of half of dataset. Therefore, the first half is "10,20,30.". Subsequently, we further dive into second half between 10 and 30, yielding 20 as Q1 value.

[Anonymous]

Hi @Nigel99,

I'd like to suggest you take a look at the following link about PERCENTILE functions if it help you to understand these functions and usages:

Regards,

XIaoxin Sheng

[Nigel99]

Unfortunately, the video does not seem to explain the function in details. 

Excel and Power BI share same function. Needless to say, both yield the same output.

If we were going to determine Q1 using conventional method, we will be sorting the group of data in ascending order (in this case, 10,20,30,40,50,60).

Q1 is deemed the half of half of dataset. Therefore, the first half is "10,20,30.". Subsequently, we further dive into second half between 10 and 30, yielding 20 as Q1 value. This baffles me as both outputs are contradicting to each other.