Forum Discussion
Percentiles in PowerBI vs Percentiles in SQL and Excel
Good afternoon,
I am currently trying to transition some reports based off SQL to PowerBI. However one sticking point I am having is that dynamically calculating percentiles in DAX using the PERCENTILE.INC function (or any of the others) gives a different result to PERCENTILE_DISC in SQL. However it appears to match those given by PERCENTILE_CONT in SQL.
Is it correct to use PERCENTILE.INC as a substitute for PERCENTILE.CONT?
If not is it possible to match percentiles calculated in SQL with PowerBI at all using any of the function?
Many thanks in advance!
1 Reply
Excel has the discrete and continuous versions of the percentile functions. In DAX, as far as I know, only the continuous version is available out of the box. However, I don't think it's that hard to write a measure that will simulate the discrete version. After all, it is based on the order statistics, which in turn can be calculated using RANKX. And also it's true that the bigger the set of observations, the closer to one another are the two statistics: the discrete and continuous percentiles.
