Search code examples
excelexcel-formulapivot-tablepowerquery

excel - group by and sum, then group by and average - pivot table of pivot table

For the below table, I am first looking to sum values by name and date. Then using that result data looking to take the average of value, grouped by name, for each month.

It seems like I want to take a take a pivot table of a pivot table. I am trying to use powerpivot although I am open to ideas.

Does anyone know how to do this?

date value name
12/29/23 50 stephen
12/29/23 20 bob
12/29/23 30 tom
12/28/23 5 stephen
12/28/23 7 bob
12/28/23 3 tom
Solution

  • Assuming there is no Excel Constraints as per the tags posted, then this should work as expected :

    • Using GROUPBY()

    enter image description here

    • Formula used in cell F3

    =LET(
     _GroupBySum, GROUPBY(HSTACK(B3:B8,D3:D8),C3:C8,SUM,0,0),
     _CombineMonthName, HSTACK(TEXT(TAKE(_GroupBySum,,1),"mmm"),INDEX(_GroupBySum,,2)),
     _Output, GROUPBY(_CombineMonthName,TAKE(_GroupBySum,,-1),AVERAGE,0,0),
     VSTACK({"Month","Name","Values"},_Output))

    Or,

    • Using PIVOTBY()

    enter image description here

    • Formula used in cell F3

    =LET(
     _PivotBySum, PIVOTBY(HSTACK(B3:B8,D3:D8),,C3:C8,SUM,0,0),
     _CombineMonthName, HSTACK(TEXT(TAKE(_PivotBySum,,1),"mmm"),INDEX(_PivotBySum,,2)),
     _Output, PIVOTBY(_CombineMonthName,,TAKE(_PivotBySum,,-1),AVERAGE,0,0),
     VSTACK({"Month","Name","Values"},_Output))

    Note: Both the functions GROUPBY() & PIVOTBY() are available in MS365 for Beta Users.

    Also I had prefer using POWER QUERY. To achieve this using the said procedure, follow this simple steps using POWER QUERY window UI:

    enter image description here

    • First convert the source ranges into a table and name it accordingly, for this example I have named it as Table_1
    • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
    • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
    let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"value", Int64.Type}, {"name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "name"}, {{"Value", each List.Sum([value]), type nullable number}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Grouped Rows", {{"date", each Date.MonthName(_), type text}}),
    #"Grouped Rows1" = Table.Group(#"Extracted Month Name", {"date", "name"}, {{"Values", each List.Average([Value]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"name", Order.Ascending}})
in
    #"Sorted Rows"

    enter image description here

    • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

    Updated with Months as headers.

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"value", Int64.Type}, {"name", type text}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"value", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each [value] <> null and [value] <> ""),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"date", "name"}, {{"Value", each List.Sum([value]), type nullable number}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Grouped Rows", {{"date", each Date.MonthName(_), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Month Name", List.Distinct(#"Extracted Month Name"[date]), "date", "Value", List.Average)
in
    #"Pivoted Column"