Can't make simple RANKX work

[Anonymous]

I'm trying to use RANKX in order to apply conditional formatting to have specific colors print for my Top 5 in a list. I can't for the life of me get my ranking to work correctly, but I have no doubt it's a simple one so I'm hoping someone can please help?

My dataset is huge and proprietary, so I'm going to just use a simple Excel example which serves the same purpose. Here is a base dataset: 

At the most basic level, I simply need to add the values of Cat2 and rank them like this:

The dataset being ranked also needs to have all filters on the page applied before the ranking. So if I only select Cat1 = XX, this would be my result:

I'm certain that is all a simple formula, but anything I try seems to give me all 1's for a rank, or something else which makes no sense. But the last thing I need, which I hope can be done, is to deal with ties by using another column (Cat2) to break the ties, as I can't have any ties if I'm going to use conditional formatting to pick 5 unique colors based on rank. So if I have a tie, I'd want to go to Cat2 alphabetically, like this:

Thank you!

[MartynRamsden]

Hi @Anonymous 

Try this:

Rank = 
RANKX(
    ALL( Table1[Cat2] ),
    CALCULATE( SUM( Table1[Value] ) )
)

There are some great blog posts about the use of RANKX.

Have a read of these:

• powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/ 
• radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns 

Hope it helps.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.