Ranking changes when applying filters

[Penn]

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.

[parry2k · Accepted solution]

@Penn your ranking expression should be as below

Ranking = RANKX(ALL('Sales'[Item Group],'Sales'[Item ID]), [Display Margin],,,Skip) 

[Penn]

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