Rankx not working in a table with multiple fields

[Anonymous]

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 🙂

[v-lili6-msft]

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/38490

Regards,

Lin

[amitchandak]

@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/367415

[az38]

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