Hi PBI community and experts!
I am using RANKX in a measure and would like to return a specific product ranking and ran into some problems.
For example, for the below product/sales list, I would like to know the Sales ranking of product G.
Product | Sales |
A | 47.11261 |
B | 490.3483 |
C | 162.2565 |
D | 981.8481 |
E | 427.8431 |
F | 994.7399 |
G | 504.8072 |
H | 850.7163 |
By creating a static “Product ranking” measure, it works fine to show Product G’s ranking (i.e. “4”) using a Multi-row card plus basic filtering (select only "G").
Product ranking = RANKX(ALL(Sheet1), CALCULATE(SUM((Sheet1[Sales]))))
| Product | Sales | Product Ranking |
| A | 47.11261 | 8 |
| B | 490.3483 | 5 |
| C | 162.2565 | 7 |
| D | 981.8481 | 2 |
| E | 427.8431 | 6 |
| F | 994.7399 | 1 |
| G | 504.8072 | 4 |
| H | 850.7163 | 3 |
In a dynamic situation with a slicer to select “Product”, the “Product ranking” measure is then amended as below:
Product ranking = RANKX(ALLSELECTED(Sheet1), CALCULATE(SUM((Sheet1[Sales]))))
Slicer selecting only A,E,G and H
| Product | Sales | Product Ranking |
| A | 47.11261 | 4 |
| E | 427.8431 | 3 |
| G | 504.8072 | 2 |
| H | 850.7163 | 1 |
However, this time the Product G’s ranking using a Multi-row card plus basic filtering will always return “1” instead of “2”, since the slicer selection flows into the “Product ranking” measure via ALLSELECTED.
How can this be fixed or is there a better way to return the ranking number of a specific product?
Many thanks for your help in advance.
Bests regards.
