Show rank in a PowerPivot

[Ashish Mathur]

Hi,

In sheet1 (you may ignore the other sheets) of this file - http://sdrv.ms/13dUFXO, I want to rank each player for each team based on R1 score. My calculated Field formula is showing a rank of 1 for all.

Why is this happening?

Thank you.

 

[Ashish Mathur]

Any help please.

 

[Jacob Barnett]

You would increase your chances of getting help by posting at least the basic details of your problem rather than expecting people to 'ferret' around inside your model - particularly when a large proportion of us don't have access to 2013 so cannot view your model or measures.

My guess is that you haven't opened the filter context - RANKX is iterative so without opening the context you are only ranking each player against themselves. Try:

= RANKX(ALL(table[PlayerName]), [Sum of R1])

You may need to adjust the ALL() depending on exactly how you want to stratify the ranking - the measure above will rank them within their teams.

 

[Ashish Mathur]

Hi,

Thank you. Your solution worked. I did not know that the measure could be a Pivot Table column. If I substitute [Sum of R1] with Sum(R1), I get all 1's.

Sorry for the skeleton explanation in my question. Next time round, I will be far more descriptive AND also attach the file to supplement understanding.

Also, this is the RANKX() formula suggested by you above, gives the same rank for ties. I would want different ranks to be give. So for example, if the numbers were 18,18,17 and 16, then the rank should be 1,2,3 and 4. I will try to do this myself. If I face a problem, I will post back.

Thank you.