Nested grouping of hierarchical items with Power Query

[Guitarmageddon]

**Title should read "Nested RANKING of hierarchical items**

Hello folks. I have some sales data that I cleaned up and grouped with power query. It distinct styles, and is sorted by a few levels of my hierarchy. You can see a snip below.

Department contains classes, contains sublcasses, contains the style number/description (1:1). I am summarizing the "ADD TTL" column which is just a demand/sales number.

I am trying to figure out how to rank the ADD TTL column just as you see below, within the SUBCLASS column. I cant quite figure this one out. I put in some crude examples over on the right of how I would expect this to look as the final product.

I started messing with Table.AddRankColumn but I think Im missing a piece. Because when I insert that after the step above, it throws the entire sort out of whack, as you can see. None of the original groupings are chunked out together and it just sorts the entire output by that "ADD TTL" column. Thoughts?

 

[jdellasala]

I suggest indicating what version of Excel you're using and on which OS, and using XL2BB to post data.
Much too difficult to transcribe even a little bit of that.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Category"}, {{"All", each Table.AddRankColumn(_, "LocalRank", {"Value", Order.Descending})}},0),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"ID", "Value", "LocalRank", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded All",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Book2
	A	B	C	D	E	F	G	H	I
1	Category	ID	Value		Category	ID	Value	LocalRank
2	A	6	150.84		A	6	150.84	3
3	A	12	201.12		A	12	201.12	2
4	A	18	87.99		A	18	87.99	5
5	A	5	213.69		A	5	213.69	1
6	A	16	113.13		A	16	113.13	4
7	B	17	226.26		B	17	226.26	1
8	B	11	75.42		B	11	75.42	7
9	B	8	125.7		B	8	125.7	5
10	B	13	62.85		B	13	62.85	8
11	B	10	163.41		B	10	163.41	3
12	B	3	100.56		B	3	100.56	6
13	B	19	188.55		B	19	188.55	2
14	B	7	50.28		B	7	50.28	9
15	B	2	138.27		B	2	138.27	4
16	C	9	12.57		C	9	12.57	5
17	C	1	175.98		C	1	175.98	2
18	C	15	238.83		C	15	238.83	1
19	C	14	37.71		C	14	37.71	3
20	C	4	25.14		C	4	25.14	4
21
Sheet2