Ranking System Error

[SinaA]

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!

[jiaopengzi]

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 ) )

[SinaA]

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!

[jiaopengzi]

You can simulate a result. I don't quite understand what you mean.

[eliasayyy]

is it possible to share some sample data please?

[SinaA]

Thanks! 

Please use the link to DL the sample data.

https://www.dropbox.com/scl/fi/yiqvlasvem3ua91bju4a0/Sales-Data-Sample.xlsx?dl=0&rlkey=xw9ai7is06l39...

Best