RANKX not working as expected

[Jos13]

Hi All,

I have a measure to rank staffs based on sales as follows :

Rank_Sales =

RANKX(ALLSELECTED('Table'[Staff]),[Sales_],,DESC)

I also have a month filter. When I filter a month for example August, I could see staffs who hadn't worked on that month.

Here Dave & Steve have not worked on August. Please tell me why is this happening & how can we solve it.

Link to pbix : 

https://drive.google.com/file/d/1R2HPsre1Jw56frrbuuwEG9TH2r72h3sn/view?usp=sharing

Thanks in advance

[richbenmintz · Accepted solution]

Hi @Jos13 ,

The Rank is working correctly, the users with no sales are ranked last, makes sense to me. what you need to do is exclude them from the rank function

like so

Rank_Sales = if(sum('Table'[Sales]) <> BLANK(), RANKX(ALLSELECTED('Table'[Staff]),[Sales_],,DESC), BLANK())

Hope this Helps

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up! 

[Jos13]

Thank you @richbenmintz . That worked for me.

[amitchandak]

@Jos13 ,

Try mutiple columns, example

City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales])

City and ID Rank = RANKX(allselected(Geography[City],Geography[City Id]),[Sales])

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/3...

[Greg_Deckler]

@Jos13 - See this article, it may help. https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452