RANKX when “Product” appears on multiple rows?

[Kirk_1]

How do I get RANKX to work on filtered data source where same product appears on multiple rows?

In this Excel example, the data loads easily into a pivot table for the desired result.

As you can see, Banana ranked #2 in the WEST, but #3 in the EAST.

What am I missing in my RANKX calculated measure?

$ RANK = RANKX(ALLSELECTED(ItemRANK[Product]), SUM(ItemRANK[Dol$ Sales]), ,DESC)

I want to be able to filter on different Time Period, different Geography, different Brands and rank to change based on slicer/filtering. Thanks.

[Kirk_1]

Hi FreemanZ

That helped, but the rank result is not correct when filtered or unfiltered.

[FreemanZ]

hi @tamerj1 

may we ask you to shed some light on this challenge?

[tamerj1 · Accepted solution]

Hi @FreemanZ 
Not 100% sure what exactly @Kirk_1's requirement is but below I'm proposing two options

[Kirk_1]

Hello @tarmerj1

THANK YOU!  Both of your options work, but option 1 is my desired result.

I am going to use:

$ RANK = RANKX(ALLSELECTED(ItemRANK), CALCULATE(SUM(ItemRANK[Dol$ Sales])), ,DESC)

The fix was to remove [Product] from ALLSELECTED. Thanks @tarmerj1 and @FreemanZ, much appreciated.

[Kirk_1]

https://we.tl/t-8hDoxhVn2j 

[FreemanZ]

hi @Kirk_1 

try like:

$ RANK = RANKX(ALLSELECTED(ItemRANK[Product]), CALCULATE(SUM(ItemRANK[Dol$ Sales])), ,DESC)