Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Average and sum while pivot column

Hello everyone,

 

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

 

CountryAttributeMonthValue
Country 1Amount

2020-01

100
Country 1Qty2020-0125
Country 1Rate2020-010.25
Country 1Amount2020-01200
Country 1Qty2020-0150
Country 1Rate2020-010.25
Country 2Amount2020-02100
Country 2Qty2020-0236
Country 2Rate2020-020.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 🙂

 

2 ACCEPTED SOLUTIONS
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Jimmy801
Community Champion
Community Champion

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"

Jimmy801_0-1609682791813.png

 

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

This is perfect!

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

Jimmy801
Community Champion
Community Champion

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"

Jimmy801_0-1609682791813.png

 

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.