In my last post I showed a new approach on how to calculate the median in PowerPivot using DAX. In the comments the question was raised whether it is possible to make that calculation universal for all kind of Quantiles like Median, Quartiles, Percentiles, etc. and that’s what this post is about.
Lets analyze the final Median calculation from the last post:
TOPN(
2-MOD([Cnt_Months], 2),
TOPN(
([Cnt_Months] + 1) / 2,
VALUES(‘Date’[Month]),
[SumSA]),
[SumSA],
1))
/
(2-MOD([Cnt_Months], 2))
The Median defines the value in the middle of an ordered set. To get the first half (+1 to handle even and odd sets) of the whole we are using TOPN function:
([Cnt_Months] + 1) / 2,
VALUES(‘Date’[Month]),
[SumSA]),
The important part here is the “divide by 2” to split the set in the middle to start our Median calculation. Instead of dividing by 2 we could also multiply by 0.5 where 0.5 would be the separator for our quantile (in this case the Median). This expression can be made dynamic. For the first Quartile we would use 0.25 for the second Quartile (=Median) we would use 0.5 and for the third Quartile we would use 0.75.
I created a little linked table to demonstrate the dynamic approach:
| Quantile | SortOrder | Percentage |
| Median | 1 | 0.5000 |
| Quartile 1 | 401 | 0.2500 |
| Quartile 2 | 402 | 0.5000 |
| Quartile 3 | 403 | 0.7500 |
| Sextile 1 | 601 | 0.1667 |
| Sextile 2 | 602 | 0.3333 |
| Sextile 3 | 603 | 0.5000 |
| Sextile 4 | 604 | 0.6667 |
| Sextile 5 | 605 | 0.8333 |
We also need to add a calculated measure that returns the percentage-value of the currently selected Quantile:
HASONEVALUE(Quantiles[Percentage]),
VALUES(Quantiles[Percentage]))
Now we can change our old Median-calculation to be dynamic by using the measure defined above:
TOPN(
2 – MOD([Cnt_Months], 2),
TOPN(
ROUNDUP(([Cnt_Months] + 1) * [SelectedQuantile], 0),
VALUES(‘Date’[Month]),
[SumSA]),
[SumSA],
1))
/
(2 – MOD([Cnt_Months], 2))
We also need to explicitly add ROUNDUP() as “([Cnt_Months] + 1) * [SelectedQuantile]” may return any decimal places whereas the previous divide by 2 could only result in a x.5 which was rounded up automatically. And well, that’s all we need to change in order to make the calculation universal for all different Quantiles! The rest is the same logic that I already described for Median calculation.
Download Final Model (Office 2013!)