Creating a ranking system for specific demographics and metrics

[DarylRob]

I have a table as below and what I want to do is to be able to create another table with all distinct asset classes and the total revenue and then a ranking based on that total revenue and i've also got a slicer on my page for region that when selected a specific region it would change the totals and rankings based just on that region selected but if no region selected then it would work for all. Hopefully that makes sense, but i'm struggling to get any sort of DAX together to make it work Asset Class Revenue 

Asset Class

Revenue	Region
Equity	100	US
PE	100	Europe
Equity	200	Asia
Credit	50	US
Credit	25	US

This is how i envision the output table to look, this would change once a region is selected as well...

Asset Class Total Revenue

Ranking
Equity	300	1
Credit	75	3
PE	100	2

[Anonymous · Accepted solution]

Hi @DarylRob ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Total Revenue = SUM('Table'[Revenue])

Rank = 
RANKX (
    ALLSELECTED ( 'Table'[Asset Class] ),
    CALCULATE ( [Total Revenue] ),
    ,
    DESC,
    DENSE
)

Best Regards

[DarylRob]

This perfect, thank you so much

[johnt75]

You could create a couple of measures like

Rev = SUM('Table'[Revenue])

Rank = 
var rev = [Rev]
return CALCULATE( 
    RANKX( 'Table', [Rev], rev ),
    ALLSELECTED('Table'[Asset Class] )
)