Forum Discussion
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 query calculation.
I'll then link the resulting % column to the other query using the combination of the columns.
| Date | Shop | Department | Sales |
| 1/1/2025 | Thompson | Electronics | 100 |
| 3/2/2025 | Thompson | Electronics | 200 |
| 2/3/2025 | Thompson | Homewears | 150 |
| 4/4/2025 | T&R | Homewears | 300 |
| 4/4/2025 | T&R | Homewears | 200 |
| 3/3/2025 | T&R | Electronics | 120 |
| 1/2/2025 | T&R | Clothing | 250 |
In Query I can group by either Shop or Department but get messed up trying to get both to do the %.
I was hoping to get to something like this.
| Dept Sales | Shop Sales | Dept as % Shop Sales | |
| Thompson Electronics | 300 | 450 | 67% |
| Thompson Homewears | 150 | 450 | 33% |
| T&R Homewears | 500 | 870 | 57% |
| T&R Electronics | 120 | 870 | 14% |
| T&R Clothing | 250 | 870 | 29% |
It seems so simple but it's doing my head in!
Any help greatfully appreciated
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
9 Replies
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
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.
Teapotlid yes it can.
Just change the source keyword to the name of last step in your query
Thanks Stéphane, this works well.
(sorry for the tardy response - life got in the way)
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"I'll review these ones over the next few days, (sorry for tardy response to your efforts)
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.
