Forum Discussion
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...
Hi, Teapotlid
Another possibilitylet
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
