Getting the PERCENTRANK.INC in PowerPIvot/DAX

Question

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)

Answer 1

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.

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)

^(Source)

Answer 2

For reference here is the DAX formula based off ashleedawg's answer which includes ignoring cells with no values in them.

=ROUNDDOWN(COUNTROWS(FILTER('Lookup_Query','Lookup_Query'[Entrances]<EARLIER('Lookup_Query'[Entrances]) && ISBLANK('Lookup_Query'[Entrances])=FALSE()))/(COUNTROWS(FILTER('Lookup_Query',ISBLANK('Lookup_Query'[Entrances])=FALSE()))-1),3)