Forum Discussion
Ranking System Error
I have a question regarding ranking salespeople based on their total sales across different cities using DAX. I'm aiming to have a consistent ranking for each salesperson, regardless of any filters applied. Currently, my ranking system functions correctly when filtering by year or city. However, I've noticed that the ranking changes when I filter for a specific salesperson. For instance, Person A is ranked 34 overall, but when I apply a filter to only display Person A, their rank changes to 21. I'm using the following DAX code:
Rank = RANKX ( ALL('SinaA'[Name]), CALCULATE (SUM('SinaA'[Sales]), ALLEXCEPT(SinaA, SinaA[Name],SinaA[City], SinaA[Year])), , DESC, Dense )
Could you provide some guidance on why this might be occurring and how to maintain a consistent ranking, regardless of the filter applied?
Thanks!
5 Replies
is it possible to share some sample data please?
Thanks!
Please use the link to DL the sample data.
Best
You can use the new function: RANK
to see if it is what you want, the first picture is unfiltered, and the second picture is added with a slicer filter_RANK = RANK ( DENSE, ALL ( Sales ), ORDERBY ( [Sales], DESC ) )I appreciate your suggestion. However, I would like to rank salespeople (A, B,....) based on their total sales. Additionally, I would like to rank them when filtering for City or Category.
Thanks!
You can simulate a result. I don't quite understand what you mean.
