Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
is it possible to share some sample data please?
Thanks!
Please use the link to DL the sample data.
Best
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |