Ranking for PowerPivot

[liybpg]

Hi I am new to PowerPivot, so just trying to get my head around few things. What I am trying to do is to create a ranking Measure which I can put in a pivot table and it would calculate a rank within that category. My dataset looks like that:

<tbody>
[TD="align: center"]Company
[/TD]
[TD="align: center"]Category
[/TD]
[TD="align: center"]Revenue
[/TD]

[TD="align: center"]Target
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]100
[/TD]

[TD="align: center"]Target
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]230
[/TD]

[TD="align: center"]A
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]100
[/TD]

[TD="align: center"]B
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]123
[/TD]

[TD="align: center"]C
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]490
[/TD]

[TD="align: center"]Target
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]839
[/TD]

[TD="align: center"]Target
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]100
[/TD]

[TD="align: center"]C
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]230
[/TD]

[TD="align: center"]D
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]498
[/TD]

[TD="align: center"]D
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]587
[/TD]

</tbody>

So what I want to be able to do is to dynamically rank ‘Target’ company without needing to display all other companies (as I have hundreds). I imagine the pivot looking something like this:

<tbody>
[TD="align: center"]Category
[/TD]
[TD="align: center"]‘Target’ Revenue
[/TD]
[TD="align: center"]‘Target’ Rank
[/TD]

[TD="align: center"]1
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]2
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]3
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: center"]Z
[/TD]
[TD="align: center"]7
[/TD]

</tbody>

Thank you!

 

[scottsen]

The function you are looking for is RANKX. You can certainly do what you want... though, I do find this particular dax function to be trickier than expected, look for samples.

 

[liybpg]

That's what I seem to find as well! Thanks anyway I'll try and decipher how to use it