Forum Discussion
Create a ranking for sensitive data
Hi fabiocovre,
Create a calculate column using DAX like below:
Total Cost = CALCULATE(SUM(Table1[Cost]), FILTER(Table1, Table1[Name] = EARLIER(Table1[Name])))
Then modify the rank measure like this:
Rank = RANKX(ALLSELECTED(Table1), CALCULATE(MAX(Table1[Total Cost]), ALLEXCEPT(Table1, Table1[Name])), , DESC, Dense)
In addtion, I'm afraid the Name column can't be removed because allselect() function are based on the Name column.
PBIX here: https://www.dropbox.com/s/utytv44dtowsqh0/Create%20a%20ranking%20for%20sensitive%20data.pbix?dl=0
Regards,
Jimmy Tao
If it is truly a calculated column in a table then it should just be:
Ranking = RANKX('BASE',[Cost])Sorry, I forgot to detail it, bit my database has many values for the same users. I would have to sum it... Is it possible to use this calculated column anyway?
The rank also should change according to the filters. Also showing 1st, 2nd, 3rd etc...
Thank you!
Hi fabiocovre,
Try DAX below:
Rank = RANKX ( ALLSELECTED ( Table1 ), CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Name] ) ), , DESC, DENSE )PBIX here: https://www.dropbox.com/s/utytv44dtowsqh0/Create%20a%20ranking%20for%20sensitive%20data.pbix?dl=0
Regards,
Jimmy Tao
Sorry, I need a table with just the rank and the total cost
In your example, the table would be:
Rank - SumCost
1 - 101515
2 - 71515
3 - 45423
The real name can't be shown in the table.
Thank you for the help.
