Forum Discussion

Teapotlid's avatar
Teapotlid
Frequent Visitor
11 months ago
Solved

Grouping 2 columns separately for a calculation

I have data similar to the table below and I'm trying to get the Department % per Shop. E.g. Thompson Electronics totals 300 & Thompson totals 450 so I want to get 67% (300/450) to use in another qu...
Need Help
  • slorin's avatar
    slorin
    11 months ago

    Hi, Teapotlid 
    Another possibility

     

    let
    Source = Your_Source,
    #"Group Shop+Dept" = Table.Group(Source, {"Shop", "Department"}, 
      {{"Dept Sales", each List.Sum([Sales]), type number}}),
    #"Group Shop" = Table.Group(#"Group Shop+Dept", {"Shop"}, 
      {{"Shop Sales", each List.Sum([Dept Sales]), type number}, 
       {"Data", each _, type table [Shop=text, Department=text, Dept Sales=number]}}),
    Expand = Table.ExpandTableColumn(#"Group Shop", "Data", {"Department", "Dept Sales"}, {"Department", "Dept Sales"}),
    #"Dept%Shop" = Table.AddColumn(Expand, "Dept as % Shop Sales", each [Dept Sales] / [Shop Sales], Percentage.Type)
    in
    #"Dept%Shop"

    Stéphane

Omid_Motamedise's avatar
Omid_Motamedise
Super User
11 months ago

Please finds the attached file including two solutions for your case.

first is using two grouping command and merging as follow

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Shop", type text}, {"Department", type text}, {"Sales", Int64.Type}}),
    Grouped1 = Table.Group(#"Changed Type", {"Shop"}, {{"Shop Sales", each List.Sum([Sales]), type nullable number}}),
    Grouped2 = Table.Group(#"Changed Type", {"Shop","Department"}, {{"Dept Sales	", each List.Sum([Sales]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(Grouped2, {"Shop"}, Grouped1, {"Shop"}, "Grouped2", JoinKind.LeftOuter),
    #"Expanded Grouped2" = Table.ExpandTableColumn(#"Merged Queries", "Grouped2", {"Shop Sales"}, {"Shop Sales"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped2", "% Shop Sales", each [#"Dept Sales#(tab)"]/[Shop Sales])
in
    #"Added Custom"

 

 

Another is using grouping and adding a column as follow

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Shop", type text}, {"Department", type text}, {"Sales", Int64.Type}}),
    Grouped2 = Table.Group(#"Changed Type", {"Shop","Department"}, {{"Dept Sales	", each List.Sum([Sales])}}),
    #"Added Custom" = Table.AddColumn(Grouped2, "Shop Sales", each List.Sum(Table.SelectRows(#"Changed Type", (x)=>x[Shop]=[Shop])[Sales]))
in
    #"Added Custom"

 

Teapotlid's avatar
Teapotlid
Frequent Visitor
11 months ago

I'll review these ones over the next few days, (sorry for tardy response to your efforts)