Getting the PERCENTRANK.INC in PowerPIvot/DAX

1
votes

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

2
exceldaxpowerpivot
Have you seen all the answer here? community.powerbi.com/t5/Desktop/PERCENTRANK-Inclusive/m-p/… – QHarr
Hi QHarr - I did see that post (see comment below on ashleedawg's answer). What I am really looking for here is a clear understanding of how Microsoft calculates PERCENTRANK.INC() and what are the difference between that and the formula in ashleedawgs answer. That post did however have and proposed solution from OwenAuger, but I can't make heads or tails of his logic in order to replicate for my use case. – Chris
Did you try implementing Owen's solution as a function in Dax? How did result compare? – QHarr
To be honest, I got lost following the logic. I haven't used vars in DAX before and was unsure why the downloaded workbook had two tables in it? If I understood what was going on, than I was hoping to replicate. – Chris
Additionally, if you take Owen's test numbers (1.0,1.5,2.0... 10) and use Excel's PERCENTRANK.INC() formula against them, you come up with different answers. – Chris

2 Answers

2
votes

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)

1
votes

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)