Search code examples
exceldaxpowerpivot

Getting the PERCENTRANK.INC in PowerPIvot/DAX

I have been tasked with converting a a worksheet from standard Excel into PowerPivot. However, I have hit a roadblock with the PERCENTRANK.INC function which is not available in DAX. I have come close to replicating it using a formula, but there are definite differences in the calculation.

Does anyone know how Excel calculates PERCENTRANK.INC()?

Formula in cell D2: =(COUNT($A$2:$A$10)-B2)/(COUNT($A$2:$A$10)-1)
Formula in cell B2: =RANK.EQ(A2,$A$2:$A$10,0)
Formula in cell C2: =PERCENTRANK.INC($A$2:$A$10,A2)

Screenshot

Solution

  • Edit:

    It's seems strange to me that there are so many "standard" ways to calculate PERCENTRANK that has have slightly different results.

    Using your example of your 9-number set of 1,2,3,4,4,6,7,8,9, depending on which "authority" I used, the third value (3) had a Percent Rank of 25.0%, 27.0%, 27.8% or 30.0%.

    Obviously we'll go with the one that gives your desired result, matching PERCENTRANK.INC.

       PERCENTRANK.INC is calculated as:

              [count of values lower than the given value]

                                 ÷

        [count of all values in the set excluding the given value]

    so, if our range of 1,2,3,4,4,6,7,8,9is in A1:A9, we could use this formula in B1:

    =COUNTIF($A$1:$A$9,"<"&A1)/(COUNT($A$1:$A$9)-1)

    ...and copy or "fill" it down for results:

    0.0%, 12.5%, 25.0%, 37.5%, 37.5%, 62.5%, 75.0%, 87.5%, 100.0%

    Original Answer

    I think you just want to calculate the PERCENTRANK for current row value. Based on the logic for PERCENTRANK, we can add a RANK column in table and achieve same logic based on this column.

    pic1

    Create a Rank column.

    Rank = RANKX(Table6,Table6[Value])

    Then create the PctRank based on the Rank column.

    PctRank = (COUNTA(Table6[Name])-Table6[Rank])/(COUNTA(Table6[Name])-1)

    pic2

    (Source)