PowerPivot and DAX papers,posts,FAQs,webcasts,samples

Lets examine what the options are for calculating Quartile / Percentiles in PowerPivot. On my last post, I explained one way to calculate Median using pure DAX. Though the calculation works, don't expect it to be speedy when dealing with large datasets; The reason for this is that it relies on row context iterations to rank values prior to selecting the median.

As similar technique can be used to calculate Quartile or Percentile, following this blog entry. However, the result is not always optimal: not only the calculation can be slow, be the results do not match Excel's native functions. The reason for this is that Excel uses interpolation to calculate both Quartile and Percentile, whereas the DAX expression above picks one value out of the set (in other words, its just an approximation).