Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kirk_1
Frequent Visitor

RANKX when “Product” appears on multiple rows?

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.

Kirk_1_3-1676841837694.pngKirk_1_4-1676841868313.png

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)

Kirk_1_0-1676841657207.png

 

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

 

1 ACCEPTED SOLUTION

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

1.png

View solution in original post

6 REPLIES 6
Kirk_1
Frequent Visitor

Hi FreemanZ

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

Kirk_1_1-1676854532797.png

 

 

 

 

hi @tamerj1 

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

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

1.png

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
Frequent Visitor

FreemanZ
Super User
Super User

hi @Kirk_1 

try like:

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.