Forum Discussion
Rankx not working in a table with multiple fields
Hi all, thanks in advance for taking the time to read my post and helping me out. I'm trying to create a ranking measure for top spenders in a travel and entertainment report and then place that ranking in a table vizualization with other relevant fields (Expense Type, Employee Name, etc.) I've done a good bit of research through this community and can't seem to identify the issue. Here's the equation I use for the ranking.
RANKX( ALLSELECTED(Table1[Employee ID]), CALCULATE(SUM(Table1[Spend USD])) , , DESC, SKIP)
I've also tried:
RANKX( ALL('Table 1'), CALCULATE(SUM(Table1[Spend USD])) , , DESC, SKIP)
The first equation returns the proper ranking but if I add any other fields into the table vizualization it will either throw off the ranking or return 1's
The second equation only returns 1's
The goal here is to be able to create this ranking and select TOPN spenders so I can put that into a slicer and allow users to select the top spenders with ease while still having the other relevant fields in the table vizualization. I haven't made enough progress with the RANKX function to test out using TOPN for a slicer but all suggestions are welcome. Let me know what I'm doing wrong here and how I should go about solving this. Thank you all so much for the input 🙂
4 Replies
- amitchandak
Super User
Anonymous , Measure Rank is context-specific
Like this my city Rank
City Rank = RANKX(all(Geography[City]),[Sales])
In case add city Id, which keys for the city in visual. I will still get 1 . Because now city is getting ranked inside city id
Now this will allow me to handle above
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales])
As both city and City Id are the same levels I can also use the one below
Geography Rank = RANKX(all(Geography),[Sales])Hope this will help
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Thanks a lot. This works
- az38
Community Champion
Hi Anonymous
try to read this RANKX bible https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
there are a lot options to evaluate rank. maybe you should use a column instead of measure. it depends on your requirements
v-lili6-msftCommunity Support
HI Anonymous
This will based on the row context in a visual, if you still have the problem, please share your sample pbix file and your expected output, that will be a great help.
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
Lin
