Forum Discussion
Ranking changes when applying filters
Hi all,
I have a example dataset that look like this.
| Item ID | Item Group | Cost | Revenue |
| ITEM001 | A | 1 | 2 |
| ITEM002 | B | 2 | 5 |
| ITEM001 | A | 1 | 3 |
| ITEM003 | A | 1 | 3 |
| ITEM002 | B | 2 | 3 |
| ITEM004 | B | 3 | 4 |
And I created measures to turn it into the table visulisation as below.
Display Revenue = CALCULATE(SUM('Sales'[Revenue]))
Display Cost = CALCULATE(SUM('Sales'[Cost]))
Display Margin = [Display Revenue] - [Display Cost]| Item ID | Item Group | Cost | Revenue | Margin |
| ITEM001 | A | 2 | 5 | 3 |
| ITEM002 | B | 4 | 8 | 4 |
| ITEM003 | A | 1 | 3 | 2 |
| ITEM004 | B | 3 | 4 | 1 |
Then I created a measure to do the ranking from the highest margin to the lowest.
Ranking = RANKX(ALL('Sales'[Item ID]), [Display Margin],,,Skip)
It works fine (as the table below) if I don't apply any filter.
| Item ID | Item Group | Cost | Revenue | Margin | Ranking |
| ITEM001 | A | 2 | 5 | 3 | 2 |
| ITEM002 | B | 4 | 8 | 4 | 1 |
| ITEM003 | A | 1 | 3 | 2 | 3 |
| ITEM004 | B | 3 | 4 | 1 | 4 |
However, when I tried to add an Item Group slicer, the ranking breaks up in a strange way. It probably won't show up with the sample dataset but it has a huge impact on the real dataset.
The expected result should be something like the table below when applying Item Group A filter.
| Item ID | Item Group | Cost | Revenue | Margin | Ranking |
| ITEM001 | A | 2 | 5 | 3 | 1 |
| ITEM003 | A | 1 | 3 | 2 | 2 |
How can I fix this? Thanks.
2 Replies
Hi there,
For some reason this meansure doesn't work for me, there is still a big jump in the rankings from 327 to 1127. It might because I have more columns in my table. Will look into this today. Thanks
