topic Re: Ranking System Error in DAX Commands and Tips

Ranking System Error

SinaA — Tue, 09 May 2023 22:41:15 GMT

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!

Re: Ranking System Error

eliasayyy — Wed, 10 May 2023 07:11:18 GMT

is it possible to share some sample data please?

Re: Ranking System Error

SinaA — Wed, 10 May 2023 19:32:30 GMT

Thanks!

Please use the link to DL the sample data.

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

Best

Re: Ranking System Error

jiaopengzi — Thu, 11 May 2023 02:50:57 GMT

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

Re: Ranking System Error

SinaA — Fri, 12 May 2023 17:12:27 GMT

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!

Re: Ranking System Error

jiaopengzi — Sat, 13 May 2023 10:33:20 GMT

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