How to add two separate Rank Column based on two criteria?

[ASG25]

Hi,
In below table, I need value base ranking by country & cust state wise in power query... 

Customer No.	Customer Name	Product Code	Product Description	Value	County	Cust State	Country Rank	State Rank
98717232	Abishek Enterprises	3457652	IMBUSH 10x4X250 ML	238339	IN	MP
98742618	NATARAJA STORES	3457144	CARATE 4x25X100 ML	2034283	IN	MP
98742618	NATARAJA STORES	3493457	FANTIS 10X1 L	324930	IN	MP
98743861	BALAJI TRADER	3437662	CARATE 2x5X1 L	349834	IN	GJ
98743861	BALAJI TRADER	3457646	IMBUSH 5x2X1 L	3487	IN	GJ
98718113	Meenashi Services	3459175	SIFLEX 25X400 ML	346837	IN	GJ
98735175	BRUNA Co.Pvt. Ltd	3469469	AMISTAR TOP 20X500 ML	56089	IN	TG
98735178	amarapuri Enterprises	3457361	ABION 40X250 ML	349837	IN	TG
98735178	amarapuri Enterprises	3457362	ABION 20X500 ML	546089	IN	TG
98735178	amarapuri Enterprises	3457363	ABION 10X1 L	2465322	IN	TG
98735178	amarapuri Enterprises	3457653	IMBUSH 25x4X100 ML	3095867	IN	TG
98744355	PAVITHRA AGENCY	3457483	ARAIFIT 50 EC 20x500 ML	4985495	IN	TG
98744355	PAVITHRA AGENCY	3457579	ABION 2X5 L	348734	IN	TG

I have tried for solution by Grouping option in power query result shows only country ranking.

Just for Example:- below First 4 Column is summarized with Pivot & then last 2 column added manually

Customer No.	Customer Name	Cust State	Value	Country Rank	State Rank
98735178	amarapuri Enterprises	TG	6457115	1	1
98744355	PAVITHRA AGENCY	TG	5334229	2	2
98742618	NATARAJA STORES	MP	2359213	3	1
98743861	BALAJI TRADER	GJ	353321	4	1
98718113	Meenashi Services	GJ	346837	5	2
98717232	Abishek Enterprises	MP	238339	6	2
98735175	BRUNA Co.Pvt. Ltd	TG	56089	7	3

[ThxAlot · Accepted solution]

[dufoq3 · Accepted solution]

Hi @ASG25, it is also possible in Power Query, but it is not the fastest one...

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZNba9tAEIX/yuLnEPYys1o9rl3ZWSPJRtoElZAHtxFEhKZBdoN+fmcVXRJiKEmFEMMM+zE65+zt7SI2kYikkouLhf3RHB/qR5Y8ner2uW2O9ZG6CjDSGOYuW16XV0zwDiqJnGUpNaUySsVhmtMn2y/uLnooSC0MdXLrbWG3lpV+VyTlABQAVK1o4hMGncRK8ABkgcgVSKM+h4wDlaq1zb0racdKsJSFJ4wlzfkZoDJaUGdpU7t1zBf2W1L0OBVpLecNZYfveBAbBSNvs/03jyQEPUuInRx5/dhEH2HCCBFEyOr66XB8aFhZty/Nz9GTWERIVenWaVIxEhAmARVoo84QFb6eWRbXuWWr35f7l9MlS0/3r2diekMKMleSvszv9kzyCvlgNGpuJp/95i00mHL4dWgPz3/a5kx8VK+KXbpdzoAP2ZmVjL5KlRN1WrSnIvzPrmqivk+RBI1Kyq9h6ehsv0S6QWIUVvEYjT4jAoDC4Nfe3jh/VVhmN0m++j4Qob8ilE+3dp6RpMmKZOgmv0hZhBg/i8UonlWtcPj9MaZz6gPv7i8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer No." = _t, #"Customer Name" = _t, #"Product Code" = _t, #"Product Description" = _t, Value = _t, Country = _t, #"Cust State" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}),
    GroupedRows1 = Table.Group(ChangedType, {"Customer No.", "Customer Name", "Country", "Cust State"}, {{"Value", each List.Sum([Value]), type number}}),
    // Added State Rank inside [All] tables.
    GroupedRows2 = Table.Group(GroupedRows1, {"Country", "Cust State"}, {{"All", each Table.AddIndexColumn(Table.Sort(_, {{"Value", Order.Descending}}), "State Rank", 1, 1, Int64.Type), type table}}),
    CombinedAll1 = Table.Combine(GroupedRows2[All]),
    // Added Country Rank inside [All] tables.
    GroupedRows3 = Table.Group(CombinedAll1, {"Country"}, {{"All", each Table.AddIndexColumn(Table.Sort(_, {{"Value", Order.Descending}}), "Country Rank", 1, 1, Int64.Type), type table}}),
    CombinedAll2 = Table.Combine(GroupedRows3[All])
in
    CombinedAll2

[ThxAlot · Accepted solution]