Forum Discussion
Combine the same date and sum up values
Hi Everyone,
I have a table with grouped Asset ID, and corresponding Date, reading. I want to combine the same date, but sum up the reading. How can I do it in Power query or by DAX?
Thank you for helping！
Hello Anonymous ,
This can be done using either Power query or DAX. I have shown both methods below:
1) Power Query
This is the sample input :
Perform the group by operation on columns ID and date, and summarize the reading column.This is the output :
Here is a sample code snippet:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrQwMTdS0lEyMtc3MNQ3MjACc4xNzJRidXDIGqJIGRrrGxjBNZqZokgaG+obGMMkTcwtlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Reading = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Reading", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Date"}, {{"Reading", each List.Sum([Reading]), type nullable number}})
in
#"Grouped Rows"2) DAX
This is the sample input table :Use the "summarize" function in DAX to create a new table and group columns.
Summarized readings =SUMMARIZE('GroupByExample-DAX','GroupByExample-DAX'[ID],'GroupByExample-DAX'[Date],"Reading", SUM('GroupByExample-DAX'[Reading]))Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊Hi Anonymous ,
I cannot understand why you would want to sort the order in the data view, unless it is a specific requirement of course!
Usually, you would add your fields to a visual on the report view, and then apply the desired sortYou also have the option to export this data.
If this does not work for you, then you can try adding an index column to your data once you have completed the sorting. Ordering the index column will ensure that your data remains in the same state as it was when you added the index.
Kind regards,
Rohit
8 Replies
Hello Anonymous ,
This can be done using either Power query or DAX. I have shown both methods below:
1) Power Query
This is the sample input :
Perform the group by operation on columns ID and date, and summarize the reading column.This is the output :
Here is a sample code snippet:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrQwMTdS0lEyMtc3MNQ3MjACc4xNzJRidXDIGqJIGRrrGxjBNZqZokgaG+obGMMkTcwtlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Reading = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Reading", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Date"}, {{"Reading", each List.Sum([Reading]), type nullable number}})
in
#"Grouped Rows"2) DAX
This is the sample input table :Use the "summarize" function in DAX to create a new table and group columns.
Summarized readings =SUMMARIZE('GroupByExample-DAX','GroupByExample-DAX'[ID],'GroupByExample-DAX'[Date],"Reading", SUM('GroupByExample-DAX'[Reading]))Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊Hi Anonymous ,
No worries. As an example, I have added another asset id to the sample data as shown below. You will notice that the dates are not sorted correctly.
You simply need to add another step in power query that sorts your table first by assetid, and then by dates in ascending order. This will ensure that all assetid's are grouped together, and their dates are in order.let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcu7DcAgDADRXVwj+QvGs6Dsv0YCCiguUp6ebgyIbi5QQByJUUhWqDW4yo9yIlYkOWOrCZWRdKN539h8nh0pJvI8JTLK52SxjI5M5yTPGO+5sD543Q==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Reading = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Reading", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Date"}, {{"Reading", each List.Sum([Reading]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"ID", Order.Ascending}, {"Date", Order.Ascending}})
in
#"Sorted Rows"
Kind regards,
Rohit
Appreciate your kudos! 😊Hi Anonymous ,
I cannot understand why you would want to sort the order in the data view, unless it is a specific requirement of course!
Usually, you would add your fields to a visual on the report view, and then apply the desired sortYou also have the option to export this data.
If this does not work for you, then you can try adding an index column to your data once you have completed the sorting. Ordering the index column will ensure that your data remains in the same state as it was when you added the index.
Kind regards,
Rohit