topic Re: RANKX when “Product” appears on multiple rows? in DAX Commands and Tips

RANKX when “Product” appears on multiple rows?

Kirk_1 — Sun, 19 Feb 2023 21:34:49 GMT

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.

FreemanZ — Sun, 19 Feb 2023 23:48:59 GMT

try like:

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

Kirk_1 — Mon, 20 Feb 2023 00:56:18 GMT

Hi FreemanZ

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

Kirk_1 — Mon, 20 Feb 2023 21:45:15 GMT

FreemanZ — Tue, 21 Feb 2023 02:24:02 GMT

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

tamerj1 — Tue, 21 Feb 2023 08:40:49 GMT

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

Kirk_1 — Tue, 21 Feb 2023 14:48:58 GMT

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.