Forum Discussion
Revising code for subtotals
Hello all -
I found some code for creating subtotals in my data which works nicely except for how it's grouping information. I want the subtotals to be on each change in Teams, not in each change of teams AND Groups. EDIT: As I typed this, I figure out how to fix this problem by removing Groups from by Table.Group statement: Table.Group(#"Sorted Rows", {"Team", "Group"},
My other issue is that I want it to group all the Group categories together. For example, rather than show all Group 2100 rows individually, I want One row for Team C, Group 2100, Description Transportation and have the October total show $42,000. Not sure how to adjust my code without impacting the calculations of the subtotals and grand totals.
A snippit of my code is below.
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Team", Order.Ascending}, {"Group", Order.Ascending}}),
Grouped = Table.Group(#"Sorted Rows", {"Team"}, {{"T", each _ & #table(Table.ColumnNames(_),{{[Team]{0} &" Total", [Group]{0} &"", [Description]{0} &"", List.Sum([Oct]), List.Sum([Nov]), List.Sum([Dec]) }} ) }}),
Combine = Table.Combine(Grouped[T]),
GrandTotal = Table.InsertRows(Combine,Table.RowCount(Combine),{[Team= "Grand_Total",Group = "", Description = "", Oct= List.Sum(#"Changed Type"[Oct]),Nov= List.Sum(#"Changed Type"[Nov]), Dec= List.Sum(#"Changed Type"[Dec]), Q1_Total= List.Sum(#"Changed Type"[Q1_Total]), Jan= List.Sum(#"Changed Type"[Jan]), Feb=List.Sum(#"Changed Type"[Feb]), Mar= List.Sum(#"Changed Type"[Mar])]})
in
GrandTotal
Hi KDS
Please provide sample data/file when asking questions so we don't have to recreate everything by hand. Without data it takes longer for you to get a reply.
Download sample PBIX with solutions.
You can do this a number of ways, in the attached file are 2 approaches, one more dynamic than the other.
Firstly, you don't need the Totals rows so delete them. Totals can be calculatd later based on the values in the other columns.
Next I'd also advise that you use a Date column that contains a date for each record, rather than having diferent columns for each month.
That said, given the data you've screenshotted, you can select the Team, Group and Desc columns then Group By and sum the Month columns. This isn't dynamic in that if you add more month columns it won't adapt.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZMxD4MgEIX/SsPscJygdO7g2KFuxoGhaUgMGLX/vyKYWtOCpu0CyfHx3oODqiInkhCkADDOZSd135pukIMy2i64OpA6iZAMNqO2SLldnNBDaQbZBLV9jMUmi3OccaWVvnnlIOAKGFYRmchzFpOiKWecHWOYADd8k3qs+HF1YW8OiMgoppOnw4uY6QcAF+0sgpaw0spcty73tm3UtX81eyqFsHPwUTLYSsJOEucH9iPZf7hT4T6D2Bh1d9L4h6S+BXzKUD8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Group = _t, Description = _t, Oct = _t, Nov = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Group", Int64.Type}, {"Description", type text}, {"Oct", Int64.Type}, {"Nov", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Team], "Total")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Team", "Group", "Description"}, {{"Oct", each List.Sum([Oct]), type nullable number}, {"Nov", each List.Sum([Nov]), type nullable number}}) in #"Grouped Rows"A dynamic approach is to select the Team, Group and Desc columns then Unpivot Other Columns. Now you can Group By the Team, Group, Desc and Attribute (month) columns, summing the Values.
Finally Pivot the Attribute column to restore individual month columns
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZMxD4MgEIX/SsPscJygdO7g2KFuxoGhaUgMGLX/vyKYWtOCpu0CyfHx3oODqiInkhCkADDOZSd135pukIMy2i64OpA6iZAMNqO2SLldnNBDaQbZBLV9jMUmi3OccaWVvnnlIOAKGFYRmchzFpOiKWecHWOYADd8k3qs+HF1YW8OiMgoppOnw4uY6QcAF+0sgpaw0spcty73tm3UtX81eyqFsHPwUTLYSsJOEucH9iPZf7hT4T6D2Bh1d9L4h6S+BXzKUD8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Group = _t, Description = _t, Oct = _t, Nov = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Group", Int64.Type}, {"Description", type text}, {"Oct", Int64.Type}, {"Nov", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Team], "Total")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Team", "Group", "Description"}, "Attribute", "Value"), #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Descending}, {"Team", Order.Ascending}, {"Group", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Team", "Group", "Description", "Attribute"}, {{"Total", each List.Sum([Value]), type number}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Total", List.Sum) in #"Pivoted Column"regards
Phil
6 Replies
Hi KDS
Please provide sample data/file when asking questions so we don't have to recreate everything by hand. Without data it takes longer for you to get a reply.
Download sample PBIX with solutions.
You can do this a number of ways, in the attached file are 2 approaches, one more dynamic than the other.
Firstly, you don't need the Totals rows so delete them. Totals can be calculatd later based on the values in the other columns.
Next I'd also advise that you use a Date column that contains a date for each record, rather than having diferent columns for each month.
That said, given the data you've screenshotted, you can select the Team, Group and Desc columns then Group By and sum the Month columns. This isn't dynamic in that if you add more month columns it won't adapt.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZMxD4MgEIX/SsPscJygdO7g2KFuxoGhaUgMGLX/vyKYWtOCpu0CyfHx3oODqiInkhCkADDOZSd135pukIMy2i64OpA6iZAMNqO2SLldnNBDaQbZBLV9jMUmi3OccaWVvnnlIOAKGFYRmchzFpOiKWecHWOYADd8k3qs+HF1YW8OiMgoppOnw4uY6QcAF+0sgpaw0spcty73tm3UtX81eyqFsHPwUTLYSsJOEucH9iPZf7hT4T6D2Bh1d9L4h6S+BXzKUD8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Group = _t, Description = _t, Oct = _t, Nov = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Group", Int64.Type}, {"Description", type text}, {"Oct", Int64.Type}, {"Nov", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Team], "Total")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Team", "Group", "Description"}, {{"Oct", each List.Sum([Oct]), type nullable number}, {"Nov", each List.Sum([Nov]), type nullable number}}) in #"Grouped Rows"A dynamic approach is to select the Team, Group and Desc columns then Unpivot Other Columns. Now you can Group By the Team, Group, Desc and Attribute (month) columns, summing the Values.
Finally Pivot the Attribute column to restore individual month columns
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZMxD4MgEIX/SsPscJygdO7g2KFuxoGhaUgMGLX/vyKYWtOCpu0CyfHx3oODqiInkhCkADDOZSd135pukIMy2i64OpA6iZAMNqO2SLldnNBDaQbZBLV9jMUmi3OccaWVvnnlIOAKGFYRmchzFpOiKWecHWOYADd8k3qs+HF1YW8OiMgoppOnw4uY6QcAF+0sgpaw0spcty73tm3UtX81eyqFsHPwUTLYSsJOEucH9iPZf7hT4T6D2Bh1d9L4h6S+BXzKUD8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Group = _t, Description = _t, Oct = _t, Nov = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Group", Int64.Type}, {"Description", type text}, {"Oct", Int64.Type}, {"Nov", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Team], "Total")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Team", "Group", "Description"}, "Attribute", "Value"), #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Descending}, {"Team", Order.Ascending}, {"Group", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Team", "Group", "Description", "Attribute"}, {{"Total", each List.Sum([Value]), type number}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Total", List.Sum) in #"Pivoted Column"regards
Phil
Thanks PhilipTreacy . Apologies for not including a file. My data/powerquery is in a separate computer and for some reason I can't access this forum from there without approval 🙄 So, emailing myself screenshots was all I could think of.
The data I import is a data entry sheet that will continue to grow and looks almost exactly like the screen shot I included. I'm not pivoting anything -- the months are already across the top. I was just asked to automate the creation of the subtotals so they don't have to do it manually since the data will change often.
I forgot to add that I'm unable to open your file so thaks for including the code.
PhilipTreacy , yes, but how I'm unsure about how I add the subtotals and the grandtotals back in. I need to provide that information.
