Ranking doesn't work with filters

[alaztor45]

Hello there,

I have a matrix in which i rank categories by their sell out. The bigger the sell out the first it comes in the ranking.

The thing is, everytime i apply a filter to see the ranking of a specific category, the rank measure just breaks and shows everything as 1. Let me show you my matrix:

STORE	CATEGORIES	Sell Out	Rank
STORE A	CATEGORY 5	118994.09	1
STORE A	CATEGORY 2	100405.01	2
STORE A	CATEGORY 4	87003.14	3
STORE A	CATEGORY 8	62858.89	4
STORE B	CATEGORY 5	87139.97	1
STORE B	CATEGORY 2	58063.21	2
STORE B	CATEGORY 4	50141.53	3
STORE B	CATEGORY 8	47140.74	4

And this is what happens when i apply a filter to let's say....category 8:

STORE	CATEGORIES	Sell Out	Rank
STORE A	CATEGORY 8	62858.89	1
STORE B	CATEGORY 8	47140.74	1

Btw, this is my ranking formula:

Sell out rank = 

IF (
    ISINSCOPE( BASE[CATEGORY] ),
    RANKX (
        CALCULATETABLE (
            VALUES ( BASE[CATEGORY]),
            ALLSELECTED ( BASE[CATEGORY])
        ),
        [Sell out]
    )
)

Any help would be greatly appreciated.

[OliT]

Hi @alaztor45 

You can try this measure

test_rank = RANKX(ALLSELECTED(BASE),[Sell Out],,DESC)

Regards,

OliT

[alaztor45]

Hi there,

Unfortunately when i apply your measure, all categories get a ranking without taking into consideration the store which they belong to. 

[lbendlin]

What is your expected result?  If you filter by category, do you want to see the store rank instead?

[alaztor45]

Yes that's the goal. If i filter by category 8 i want the same rank that i would get when all of the categories are ranked.

Is that possible with a measure?

[lbendlin]

Either use a calculated column for the rank, or create a new measure that expands the filter context accordingly (remove filters for category but keep filters for store)