none
PowerPivot DAX support for Quantile (Percentile and Quartile)

    General discussion

  • Hello,

    I recently needed to use create a DAX Measure that would return the Percentile of my serie but hit a wall as the percentile function was nowhere to be seen.

    Thanks to Frankie Yuen post and Marco Russo's post about the MEDIAN, i found a way to compute the percentile as such :

    MINX(FILTER(VALUES(MyDataSerie1[Col1]),
              CALCULATE(COUNTROWS(MyDataSerie1),
                   MyDataSerie1[Col1] <= EARLIER(MyDataSerie1[Col1]) )
              > COUNTROWS(MyDataSerie1)*0.9),
       MyDataSerie1[Col1])

    It's working quite well so far but it is just so frustrating to use such a complex template for something that seems so common.

    Have I missed a function that would allow me to compute quantiles easily ? Do you have other suggestions ?

    Regards,

    Jonathan

    Monday, August 23, 2010 4:03 PM

All replies

  • Hi Jonathan

    Does the above quartile calculation accommodate both odd and even number data sets?

    Paul

    Sunday, September 08, 2013 2:18 PM
  • Check out Gerhard Brueckl's post on universal quantiles here.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    Sunday, September 08, 2013 10:19 PM
    Answerer
  • I tried Gerhard Brueckl's version, but it did not work for me. The above formula is the only one that seems to somewhat work so far.

    Paul

    Monday, September 09, 2013 12:37 PM