Ranking over multiple categories

Question

Hello,&nbsp;I have a table that breaks down sales for different stores to multiple categories/subcategories. I am trying to find the top two category/subcagories for each store by spend and also calculate the % of spend over each category.&nbsp;If it was just the stores, I'd do Group By in Power Query, and do sum of spend, sort it in descending order and then create an index column. But I'm not sure how to do it with the nested categories/subcategories within the Stores. Also I'm not sure if the % of Spend over each store is possible in power query or do I do it in DAX. My table is as follows:&nbsp;&nbsp;Main Table:&nbsp;StoreCategoriesSubcategorySpendAClothingMen47AClothingWomen26AShoesKids75AHousewaresDishes17BBooksFiction64BStationaryPaper72BBagsHandbags95BBaby ItemsToys22CBakery ItemsBread31CBeveragesWater63CHousewaresCandles22CProduceFruits74CProduceVegetables70DBakery ItemsCakes54DProduceFruits81DProduceVegetables29DBakery ItemsBread52EBooksFiction28EStationaryPens11EStationaryPencils57EStationaryMarkers38EStationaryPaper99FProduceFruits83FDairyMillk40FDairyCheese79FProduceVegetables89&nbsp;I'd like to first calculate the rank:&nbsp;StoreCategoriesSucategorySpendRankAClothingMen472AClothingWomen263AShoesKids751AHousewaresDishes174BBooksFiction643BStationaryPaper722BBagsHandbags951BBaby ItemsToys224CBakery ItemsBread314CBeveragesWater633CHousewaresCandles225CProduceFruits741CProduceVegetables702DBakery ItemsCakes542DProduceFruits811DProduceVegetables294DBakery ItemsBread523EBooksFiction284EStationaryPens115EStationaryPencils572EStationaryMarkers383EStationaryPaper991FProduceFruits832FDairyMillk404FDairyCheese793FProduceVegetables891&nbsp;And the final result would be as follows:&nbsp;StoreCategorySubcategorySpendRank% of SpendAShoesKids75145%AClothingMen47228%BBagsHandbags95138%BStationaryPaper72228%CProduceFruits74128%CProduceVegetables70227%DProduceFruits81138%DBakery ItemsCakes54225%EStationaryPaper99142%EStationaryPencils57224%FProduceVegetables89131%FProduceFruits83229%&nbsp;Would anyone know how I could do this? I'd really appreciate any help.&nbsp;&nbsp;Thank you!

ddpl · Accepted Answer

newpbiuser01&nbsp;&nbsp;Create 2 calculated column as below...&nbsp;Rank = RANKX(FILTER('Table', 'Table'[Store] = EARLIER('Table'[Store])), 'Table'[Spend])&nbsp;% Spent = DIVIDE(&nbsp; &nbsp; 'Table'[Spend],&nbsp; &nbsp; SUMX(FILTER('Table', 'Table'[Store] = EARLIER('Table'[Store])), 'Table'[Spend]))&nbsp;&nbsp;

Forum Discussion

Ranking over multiple categories

2 Replies

Related content

RANK by category?

Ranking Sales By Multiple Categories

Ranking by multiple category

Ranking by category

Duplicate ranking

Store	Categories	Subcategory	Spend
A	Clothing	Men	47
A	Clothing	Women	26
A	Shoes	Kids	75
A	Housewares	Dishes	17
B	Books	Fiction	64
B	Stationary	Paper	72
B	Bags	Handbags	95
B	Baby Items	Toys	22
C	Bakery Items	Bread	31
C	Beverages	Water	63
C	Housewares	Candles	22
C	Produce	Fruits	74
C	Produce	Vegetables	70
D	Bakery Items	Cakes	54
D	Produce	Fruits	81
D	Produce	Vegetables	29
D	Bakery Items	Bread	52
E	Books	Fiction	28
E	Stationary	Pens	11
E	Stationary	Pencils	57
E	Stationary	Markers	38
E	Stationary	Paper	99
F	Produce	Fruits	83
F	Dairy	Millk	40
F	Dairy	Cheese	79
F	Produce	Vegetables	89

Store	Categories	Sucategory	Spend	Rank
A	Clothing	Men	47	2
A	Clothing	Women	26	3
A	Shoes	Kids	75	1
A	Housewares	Dishes	17	4
B	Books	Fiction	64	3
B	Stationary	Paper	72	2
B	Bags	Handbags	95	1
B	Baby Items	Toys	22	4
C	Bakery Items	Bread	31	4
C	Beverages	Water	63	3
C	Housewares	Candles	22	5
C	Produce	Fruits	74	1
C	Produce	Vegetables	70	2
D	Bakery Items	Cakes	54	2
D	Produce	Fruits	81	1
D	Produce	Vegetables	29	4
D	Bakery Items	Bread	52	3
E	Books	Fiction	28	4
E	Stationary	Pens	11	5
E	Stationary	Pencils	57	2
E	Stationary	Markers	38	3
E	Stationary	Paper	99	1
F	Produce	Fruits	83	2
F	Dairy	Millk	40	4
F	Dairy	Cheese	79	3
F	Produce	Vegetables	89	1

Store	Category	Subcategory	Spend	Rank	% of Spend
A	Shoes	Kids	75	1	45%
A	Clothing	Men	47	2	28%
B	Bags	Handbags	95	1	38%
B	Stationary	Paper	72	2	28%
C	Produce	Fruits	74	1	28%
C	Produce	Vegetables	70	2	27%
D	Produce	Fruits	81	1	38%
D	Bakery Items	Cakes	54	2	25%
E	Stationary	Paper	99	1	42%
E	Stationary	Pencils	57	2	24%
F	Produce	Vegetables	89	1	31%
F	Produce	Fruits	83	2	29%