Ranking over multiple categories

[newpbiuser01]

Hello,

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.

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: 

Main Table: 

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

I'd like to first calculate the rank: 

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

And the final result would be as follows: 

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%

Would anyone know how I could do this? I'd really appreciate any help. 

Thank you!

[ddpl · Accepted solution]

@newpbiuser01 

Create 2 calculated column as below...

Rank = RANKX(FILTER('Table', 'Table'[Store] = EARLIER('Table'[Store])), 'Table'[Spend])

% Spent = 
DIVIDE(
    'Table'[Spend],
    SUMX(FILTER('Table', 'Table'[Store] = EARLIER('Table'[Store])), 'Table'[Spend])
)

[newpbiuser01]

Thanks @ddpl! This worked beautifully!