0

I am trying to rank the values in a calculated field called PMPM in powerpivot. The formula I'm using is

RANKX(ALLSELECTED(Cost),[PMPM],,TRUE(),Dense)

but the results are wrong. Sample data here

The first column PMPM contains the fields I want to rank, PMPM2 contains the correct ranking that I put in manually for comparison. PMPMRank is the calculated field for which I use the formula above, but I don't get the correct results.

I'd appreciate any help! Thanks!

Priyanka Zaveri
  • 5
  • 4

2 Answers2

0

Your formula works for me...?

Incidentally, your sample data is very strange - the ranking for >10 rank miss out some steps. e.g. rank 11. This isn't skip behaviour either (from what I can see of your sample data).

If you want to change the skip/dense behaviour in PP, you can insert an IF() function to choose how it behaves. For example, the below forces the RANKX column to change to SKIP instead of DENSE after the 10th ranking...

=
if(
 RANKX('table',[PMPM],,TRUE(),DENSE)<=10
,RANKX('table',[PMPM],,TRUE(),DENSE)
,RANKX('table',[PMPM],,TRUE(),SKIP)
)

These are the results I get for the "dynamic" skip dense behaviour change

dijksterhuis
  • 1,225
  • 11
  • 25
0

=IF(N(D2),INDEX($B$2:$B$2900,SMALL(IF($A$2:$A$2900=D2,ROW($B$2:$B$2900)-ROW($B$2)+1),COUNTIF(D$2:D2,D2))),"")

Make sure to press CTRL + ENTER otherwise this formula will not work.

J.1
  • 20
  • 2
  • 7