Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm working with medical data, and due that, I cannot show the name of the people in my report.
I have this following table in my dashboard:
Name - Cost
John - 50,000
Mark - 20,000
Peter - 10,000
And I need to change the collumn "Name" for a collumn called "Ranking". Example below:
Ranking - Cost
1 - 50,000
2 - 20,000
3 - 10,000
I tried to do this formula "Ranking = RANKX(ALLSELECTED('_BASE'[Name]);[SumCost])", but when I replace the "Name" field in my table for "Ranking", this is the result.
Ranking - Cost
1 - 80,000
Is it possible to be done?
Thank you!
Solved! Go to Solution.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |