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 was trying to make a rank measure to take rank Customer based on their Sales. However, I need to avoid certain customers such as XXX and blank customers in the ranking or add them to a default ranking. Unfortunately, it is not working for me. Below is an example and the expected output as Rank.
Customer | Sales | Rank |
E | 5000 | 1 |
C | 2000 | 2 |
XXX | 600 | |
B | 500 | 3 |
200 | ||
Z | 150 | 4 |
A | 100 | 5 |
T | 50 | 6 |
R | 10 | 7 |
The dax calculation I am using is
Rank =
IF (
SELECTEDVALUE ( Query1[Customer] ) IN { "XXX", "" }, -100, //applying default rank of -100
RANKX (
FILTER ( ALL ( Query1 ), NOT ( Query1[Customer] IN { "XXX", "" } ) ),
[Sales],
,
DESC
)
)
Please help
Solved! Go to Solution.
I managed to frame the dax with the tips from @DataInsights . Thanks. Below is the final working solution
Ranking Customers Asc =
IF (
NOT MAX ( Query1[Customers] ) IN { "XXX", BLANK (), "" },
//Filtering unwanted customers like XXX and blanks
CALCULATE (
RANKX ( ALL ( Query1[Customers] ), [Sales],, ASC, DENSE )
),
//applying rank
100 //giving default rank of 100 to unwanted customer
)
I managed to frame the dax with the tips from @DataInsights . Thanks. Below is the final working solution
Ranking Customers Asc =
IF (
NOT MAX ( Query1[Customers] ) IN { "XXX", BLANK (), "" },
//Filtering unwanted customers like XXX and blanks
CALCULATE (
RANKX ( ALL ( Query1[Customers] ), [Sales],, ASC, DENSE )
),
//applying rank
100 //giving default rank of 100 to unwanted customer
)
Try this measure:
Rank =
IF (
NOT MAX ( Query1[Customer] ) IN { "XXX", "" },
RANKX (
FILTER ( ALL ( Query1 ), NOT Query1[Customer] IN { "XXX", "" } ),
[Sales],
,
DESC
)
)
Proud to be a Super User!
Thank you for the help. Unfortunately, it is not working for me. The ranking is random. Please see below screenshot. I am checking on the worst profit (or loss) ranking.
Please provide a sanitized pbix using one of the file services (e.g., OneDrive) and I'll look at it.
Proud to be a Super User!
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 |
---|---|
103 | |
101 | |
84 | |
77 | |
66 |
User | Count |
---|---|
135 | |
109 | |
104 | |
83 | |
73 |