How to find group name with max value based on Sum after group by. Also, for each year and product

Hi skrishpv ,

1. Choose Groupby :

2.Add a Custom column:

3. Then expand the "Table" and "Record" to keep the necessary columns.

Here is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUTI0MgaRBgZA0is/I08pVgcsZwkSNTEFkkZgueDEXJgUQhuGFEKXKbqBCF3GeCwzwWoZWAqHG7E7xAK38xG6THFqMsatyQTJGbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, ProductID = _t, Sales = _t, Salesperson = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"ProductID", Int64.Type}, {"Sales", Int64.Type}, {"Salesperson", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "ProductID"}, {{"All Rows", each _, type table [Year=nullable number, ProductID=nullable number, Sales=nullable number, Salesperson=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([All Rows],"Sales")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Salesperson"}, {"Who sold max.Salesperson"}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Expanded Custom", "All Rows", {"Sales", "Salesperson"}, {"Sales", "Salesperson"})
in
    #"Expanded All Rows"

Final output:

Or you could create a measure using  DAX:

Measure = 
var _max=MAXX(FILTER(ALL('Table'),[Year]=MAX('Table'[Year]) && [ProductID]=MAX('Table'[ProductID])),[Sales])
return CALCULATE(MAX('Table'[Salesperson]),FILTER(ALL('Table'),[Year]=MAX('Table'[Year]) && [ProductID]=MAX('Table'[ProductID])&&[Sales]=_max))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.