Forum Discussion

skrishpv's avatar
skrishpv
New Member
4 years ago
Solved

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

I have a dataset like the one below,

Now I want to know for each year which salesperson made the most sales for each product. Like in 2018 most sales for Product 123 was made by John. 

This value has to be displayed against each row for product 123 in year 2018.Like below

I need this to create a table visual eventually. 

Any help would be greatly appreciated!!

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    You can group on year and product ID, use the Sim aggregation on the Sales column, name the column Sum_Sales, and also make an AllRows column, named Details.

    Now you can add custom column:

     

    Taable.AddColumn(PriorStepOrTableName,"Top", each Table.Max(_[Details], "Sum_Sales")[[Sum_Sales], [SalesPerson]])

    Then you can expand the new table column.

     

    --Nate

    • skrishpv's avatar
      skrishpv
      New Member
      4 years ago

      Hi Nate,

       

      Thanks for your response.

      I am getting below error when creating the custom column,

      Expression.Error: The specified sort criteria is invalid.
      Details:
      Sum_Sales

       

      Could you help me understand this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.