Forum Discussion

Sid10's avatar
Sid10
Icon for Helper I rankHelper I
6 years ago
Solved

Calculating Percentiles in PowerBI

In Excel, we can use percentrank.inc function to calculate the percentile scores for a list of values. How do I accomplish the same in powerbi? So, for illustration, say, I have a set of 7 values - For each value I want to know what is its percentile score. I hope to then put each value in different quartiles.

I was able to calculate the 'rank', if that helps.  

Rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Value])))

 

Need Help
  • Tahreem24's avatar
    Tahreem24
    6 years ago

    Try this DAX:

     

    Percentile = RANKX ( ALL ( TableName ), CALCULATE ( SUM ( TableName[Value] ) ), , ASC, SKIP ) / COUNTX ( ALL ( TableName ), TableName[Value] )

     

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    Try this DAX:

     

    Percentile = RANKX ( ALL ( TableName ), CALCULATE ( SUM ( TableName[Value] ) ), , ASC, SKIP ) / COUNTX ( ALL ( TableName ), TableName[Value] )

     

    • Sid10's avatar
      Sid10
      Icon for Helper I rankHelper I
      6 years ago

      Tahreem24 Thank you for that elegant solution to get to percentiles using a simple Rank and Count function!

      There is a small error in your formula though. Percentile = R/(n+1) [In other words you need to add 1 to the denominator]. Please edit your answer so others are not misguided on this. 

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi  Sid10 

        Just adjust the rank measure as below:

        Rank 1 = RANKX(ALL('Table'[Name]),CALCULATE(SUM('Table'[Value])))

        or

        Rank 2 = RANKX(ALLSELECTED('Table'[Name]),CALCULATE(SUM('Table'[Value])))

        Result:

         

        Regards,

        Lin

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Sid10 According to Excel to DAX Translation: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

    https://community.powerbi.com/t5/Community-Blog/P-Q-Excel-to-DAX-Translation/ba-p/1061107

    PERCENTRANK Equivalent is here:

    https://community.powerbi.com/t5/Quick-Measures-Gallery/PERCENTILERANK/td-p/1082573

    and kind of looks like this:

    PERCENTILERANK.INC = 
    VAR __Value = MAX('RanksInc'[Rank])
RETURN
    IF(
        __Value IN SELECTCOLUMNS('ValuesInc',"Values",[Value]),
                VAR __NumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Value))
                VAR __NumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Value))
                VAR __Rank = __NumLower / (__NumLower + __NumHigher)
            RETURN IF(ISBLANK(__Rank),0,__Rank),
                VAR __Lower = MAXX(FILTER('ValuesInc',[Value] < __Value),[Value])
                VAR __Higher = MINX(FILTER('ValuesInc',[Value] > __Value),[Value])
                VAR __LowerNumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Lower))
                VAR __LowerNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Lower))
                VAR __LowerRank = __LowerNumLower / (__LowerNumLower + __LowerNumHigher)
                VAR __HigherNumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Higher))
                VAR __HigherNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Higher))
                VAR __HigherRank = __HigherNumLower / (__HigherNumLower + __HigherNumHigher)
            RETURN
                __LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank )
    )