Combine the same date and sum up values

[Anonymous]

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！

[rohit_singh · Accepted solution]

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! 😊

[rohit_singh]

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! 😊

[Anonymous]

Hi Rohit,

Thank you for your help! I followed your instruction, it works very well. But I am facing another problem. Once I save and closed the PQ, the order is messed up again! 😞
I tried using Table.Buffer=(#"Sorted Rows") to lock the order, but failed. I am stuck here , I really need help!

[rohit_singh · Accepted solution]

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 sort 

You 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

[Anonymous]

Hi Rohit,

Adding index works the best!  I really appreciate your help!!!

[Anonymous]

I need to do more calculation with DAX in the data view(not good at M in PQ :)). I need to calculate the date different of each record and find out the average.

[rohit_singh]

Hi @Anonymous ,

No worries. Maybe this helps : 

I have added the following columns assuming the calculation is for each id

Prev Date =

CALCULATE(

MAX(GroupByExample[Date]),

FILTER(GroupByExample, GroupByExample[ID] = EARLIER(GroupByExample[ID]) &&

GroupByExample[Date] < EARLIER(GroupByExample[Date])

))

DateDiff = DATEDIFF(GroupByExample[Prev Date], GroupByExample[Date], day)

Avg Date Diff =

CALCULATE(ROUND(AVERAGE(GroupByExample[DateDiff]),0),

FILTER(GroupByExample, GroupByExample[ID] = EARLIER(GroupByExample[ID])

)

)

Not sure if this is what you were looking to do but I hope it helps.

Kind regards,

Rohit

[rohit_singh · Accepted solution]

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! 😊

[Anonymous]

Hi Rohit,

Thank you so much for your reply!!! The problem seems addressed, but there is is still a issure: the order of the date is messed up, how can i sort it keeping the same Asset ID? I am using the first method which group in Power query.