Forum Discussion
Ranking Column by Sub Category (RANKX)
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
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
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.
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