Grouping 2 columns separately for a calculation

[Teapotlid]

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

[v-venuppu]

Hi @Teapotlid ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @Teapotlid ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

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.

[slorin · Accepted solution]

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]

Thanks Stéphane, this works well.

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

[Teapotlid]

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]

@Teapotlid yes it can.

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

[Omid_Motamedise]

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]

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