Sum of aggregated data

Hi amiskow 

This can be best done in PQ. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIGYicgdgFiIyA2gdKGSrE60WAZR6gKkEpjIDaHqjAFq3CG6nWBqgLpNwPrB7FBKlygZrhCaUOoOSAzLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, P2 = _t, P3 = _t, P4 = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"P1", "P2", "P3", "P4"}),
    T1_ = Table.UnpivotOtherColumns(#"Removed Other Columns", {}, "Attribute", "Value"),
    auxT2_ = Table.SelectColumns(#"Changed Type",{"Q1", "Q2", "Q3", "Q4"}),
    T2_ = Table.UnpivotOtherColumns(auxT2_, {}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(T2_, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each T1_[Value]{[Index]}, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom"}, {{"Count", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers