Total and Rank

[AllanBerces]

Hi good day can anyone help me on my PQ, what we required is the Total sum of trade and Rank it from Highest to Lowest. if the Rank 1 is two date let say 5/14 and 5/13 used the latet date.

RESULT

DATE	Direct and InDirect	Com	Sum Trade	Total Trade	Rank
5/14/2025	E&I Artisan	E&I	8	113	1
5/14/2025	Banksman	Scaff	42	113	1
5/14/2025	Marker	Mech	63	113	1
5/9/2025	E&I Artisan	E&I	7	78	2
5/9/2025	Banksman	Scaff	33	78	2
5/9/2025	Marker	Mech	38	78	2
6/12/2025	E&I Artisan	E&I	3	74	3
6/12/2025	Banksman	Scaff	48	74	3
6/12/2025	Marker	Mech	23	74	3
5/7/2025	E&I Artisan	E&I	7	71	4
5/7/2025	Banksman	Scaff	36	71	4
5/7/2025	Marker	Mech	28	71	4
5/8/2025	E&I Artisan	E&I	4	68	5
5/8/2025	Banksman	Scaff	34	68	5
5/8/2025	Marker	Mech	30	68	5
5/6/2025	E&I Artisan	E&I	6	56	6
5/6/2025	Banksman	Scaff	37	56	6
5/6/2025	Marker	Mech	13	56	6

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(
        Source, 
        "DATE", 
        {
            {"tbl", (x) => x}, 
            {"Total trade", (x) => List.Sum(x[Sum Trade])}
        }
    ),
    rank = Table.AddRankColumn(
        group, 
        "Rank",
        {{"Total trade", Order.Descending}, {"DATE", Order.Descending}}, 
        [RankKind = RankKind.Ordinal]
    ),
    xpand = Table.ExpandTableColumn(rank, "tbl", List.RemoveItems(Table.ColumnNames(Source), {"DATE"}))
in
    xpand

[AllanBerces]

Hi @AlienSx thank you very much how can i add that to my current query

let
Source = #"Complete_List Actual POB",
#"Grouped Rows" = Table.Group(Source, {"DPR_DATE", "Combine Direct and InDirect", "BP's"}, {{"Sum Trade", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"

[AlienSx · Accepted solution]

hello, @AllanBerces . Create new (blank) query and replace everything inside with this code:

let
    // your code 
    Source = #"Complete_List Actual POB",
    your_group = Table.Group(
        Source, 
        {"DPR_DATE", "Combine Direct and InDirect", "BP's"}, 
        {{"Sum Trade", each Table.RowCount(_), Int64.Type}}
    ),
    // my code
    my_group = Table.Group(
        your_group, 
        "DPR_DATE", 
        {
            {"tbl", (x) => x}, 
            {"Total Trade", (x) => List.Sum(x[Sum Trade])}
        }
    ),
    rank = Table.AddRankColumn(
        my_group, 
        "Rank",
        {{"Total Trade", Order.Descending}, {"DPR_DATE", Order.Descending}}, 
        [RankKind = RankKind.Ordinal]
    ),
    xpand = Table.ExpandTableColumn(rank, "tbl", List.RemoveItems(Table.ColumnNames(your_group), {"DPR_DATE"}))
in
    xpand

[AllanBerces]

Hi @AlienSx thank you very much for the reply working perfectly