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

v-venuppu's avatar
v-venuppu
Community Support
11 months ago

Hi Teapotlid ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you Omid_Motamedise slorin  for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.