Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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
This perfect, thank you so much
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] )
)
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |