Ranking Column by Sub Category (RANKX)

[Anonymous]

Hello Everyone,

I have a feeling that this question has already been answered. But I still have to ask again. I found a few posts here explaining similar situations. For some reason solutions did not work for me. Not sure if this is my fault or it actually is a different case.

Need to create a ranking colulmn in PowerPivot - manufacturer sales within a product category. Based on the ranking manufacturers will be assigned different names/group names. So, rank 1 in product category A may become "Awesome", but rank 1 in product category B may become "Just OK". I am attaching screen for your reference.

Also, the data set has transactions for two years (2017-18). The ranking needs to be done only by 2018 sales.

Any ideas? My feeling is RANKX needs to be utilized. 

[samdthompson]

Hello, try this:

=RANKX(FILTER(Table1,[Manufacturer_ID]=EARLIER([Manufacturer_ID])),CALCULATE(sum(Table1[Amount]),ALLEXCEPT(Table1,Table1[Product_ID],Table1[Manufacturer_ID])))

here it is working, note that the expected rank of AAA/790 should be 1 given that its the largest of the 3 manufacturers for sales of that product:

 

// if this is a solution please mark as such

[Anonymous]

Thank you for quick reply. Unfortunately, the solution is different from what is needed. The data I show in my original post is not complete and cannot be used. I pasted this sub set only to demonstrate the data structure. Let me try to explain the end result again.

The data set contains sales by manufacturer and product category in years 2017 and 2018.

Manufacturers are ranked by YTD sales in 2018 within a product category.

The rank 1 should be assigned to every line in transactions table for a largest manufacturer of this particular product.

Exapmle: AAA is number one by sales volume in 2018 for product X --> every transaction for manufacturer AAA and product X should be marked as 1 in "Rank" column, regardless of the year.

I hope this clarifies the case. Thank you all in advance.

[samdthompson · Accepted solution]

Ok, do this:

Amount Measure:=sum([Amount])

Rank Measure:=RANKX(ALL(Table1[Manufacturer_ID]),[Amount Measure])

and heres it working regardless of year:

If this doesnt work you might need to post a picture of your data structure so we can have a look at tables that need to be taken account of. There is also some great info here: https://powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/  

// if this is a solution please mark as such