Revising code for subtotals

[KDS]

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

[KDS]

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.

[PhilipTreacy]

Hi @KDS 

So my code worked for you then?

regards

Phil

[KDS]

@PhilipTreacy , yes, but how I'm unsure about how I add the subtotals and the grandtotals back in.  I need to provide that information.  

[PhilipTreacy]

Hi @KDS 

Calculating sub-totals and totals should be done in Excel or Power BI, not inside Power Query.

PQ is used to prepare the data and get it into a format that it can be used in Pivot Tables (Excel) or Power BI visuals that calculate (sub)totals for you.

If you add (sub)totals into the raw data with PQ you will end up with issues when you try to analyse this data as you will then have to exclude the (sub)totals from any calculations you do in Excel/Power Query.

Going a litle further with this idea, perhaps even grouping the data as you have asked here is not necessary as this is also done by Pivot Tables/Power BI.  Really the ideal format for data is a single row per transaction/record - this is known as a tabular format.

Data in a tabular format is easily grouped, totalled etc by Excel and Power BI.

Perhaps if you give an example of the end result you are after in terms of what report/visual you want and the data it should show, I can help you get that

Regards

Phil

[KDS]

I forgot to add that I'm unable to open your file so thaks for including the code.

[PhilipTreacy · Accepted solution]

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