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

slorin's avatar
slorin
Super User
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

  • Teapotlid's avatar
    Teapotlid
    Frequent Visitor
    11 months ago

    Thanks Omid_Motamedise  & slorin .

    Can the "source" be an earlier row in the query? My data comes from another query which I referenced to build this query and I have some earlier steps where I remove unnecessary columns.

    • Omid_Motamedise's avatar
      Omid_Motamedise
      Super User
      11 months ago

      Teapotlid yes it can.

      Just change the source keyword to the name of last step in your query 

  • Teapotlid's avatar
    Teapotlid
    Frequent Visitor
    10 months ago

    Thanks Stéphane, this works well.

    (sorry for the tardy response - life got in the way)