Average and sum while pivot column

[Anonymous]

Hello everyone,

I am trying to pivot a table with similar data as below to have the months in column.

Country	Attribute	Month	Value
Country 1	Amount	2020-01	100
Country 1	Qty	2020-01	25
Country 1	Rate	2020-01	0.25
Country 1	Amount	2020-01	200
Country 1	Qty	2020-01	50
Country 1	Rate	2020-01	0.25
Country 2	Amount	2020-02	100
Country 2	Qty	2020-02	36
Country 2	Rate	2020-02	0.36

I want the Amount and Qty values to be summed up but the rate to be an average for the month (pivoted column). 

My issue is that I don't know how to specify it when applying my pivot step, I only came up with this so far which does not specify that I want Rate to be an average :

= Table.Pivot(#"previous step", List.Distinct(#"previous step"[Month]), "Month", "Value", List.Sum)

Many thanks for your help 🙂

[mahoneypat · Accepted solution]

Here is one way to do it with Group By before the Pivot.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlQwVNJRcswFcYAMIwMjA10DkJChgYFSrA6qssCSShQ1RqYYSoISS1JR1BjoYVGFxT4jIuwzxVRC2D4jTPuMsPjPCM0+EN/YDEMJin1GYPtAqmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Attribute = _t, Month = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Attribute", type text}, {"Month", type date}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "Month", "Attribute"}, {{"ValueSum", each List.Sum([Value]), type nullable number}, {"ValueCount", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Value", each if [Attribute] = "Rate" then [ValueSum]/[ValueCount] else [ValueSum], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ValueSum", "ValueCount"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Regards,

Pat

[Anonymous]

This is perfect!

Thank you guys, I will try to think Table.Group next time I have a similar challenge 😉

[Jimmy801 · Accepted solution]

Hello @Anonymous 

there is no way to do this with Pivot but with Table.Group. You can pack all your requirements in the functions of Table.Group. The trick is to filter the grouped table before summing or averaging. Here an example

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlQwVNJRcswFcYAMIwMjA10DkJChgYFSrA6qssCSShQ1RqYYSoISS1JR1BjoYVGFxT4jIuwzxVRC2D4jTPuMsPjPCM0+EN/YDEMJin1GYPtAqmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Attribute = _t, Month = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Attribute", type text}, {"Month", type text}, {"Value", type number}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "Month"}, {{"Amount", each List.Sum(Table.SelectRows(_,each [Attribute]="Amount")[Value]), type number}, {"Qty", each List.Sum(Table.SelectRows(_,each [Attribute]="Qty")[Value]), type number}, {"Rate", each List.Average(Table.SelectRows(_,each [Attribute]="Rate")[Value]), type number}})
in
    #"Grouped Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mahoneypat · Accepted solution]

Here is one way to do it with Group By before the Pivot.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlQwVNJRcswFcYAMIwMjA10DkJChgYFSrA6qssCSShQ1RqYYSoISS1JR1BjoYVGFxT4jIuwzxVRC2D4jTPuMsPjPCM0+EN/YDEMJin1GYPtAqmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Attribute = _t, Month = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Attribute", type text}, {"Month", type date}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "Month", "Attribute"}, {{"ValueSum", each List.Sum([Value]), type nullable number}, {"ValueCount", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Value", each if [Attribute] = "Rate" then [ValueSum]/[ValueCount] else [ValueSum], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ValueSum", "ValueCount"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Regards,

Pat

[StefanoGrimaldi]

cant be done inside the same step as far as I know, you would need to split the query in various one to threat each attribute and them combine or append the queries back as one, if you cahnge the pivot step to average it will apply to all. so only seeing this happening by spliting the query work it and unify it them