Forum Discussion
Combine Rows for Single Order in DAX
I am trying to combine rows with the same in DAX for a visual. Right now the DB is showing this as two or multiple different lines based on quantity status. The top chart shows how it looks currently. The bottom chart is how I would like it to look in my visual. There are many other columns that would be displayed, but this is the only part I am struggling to make happen. I am open to a M Query solution as well, but thought there should be some way to do this in DAX.
| SO - Doc & Line | Line Item Qty | OBD Line Item Qty |
| 550781507710 | 0 | 900 |
| 550781507710 | 0 | 1200 |
550781507710 | 4800 | |
550781507710 | 0 | 600 |
| SO - Doc & Line | Line Item Qty | OBD Line Item Qty | Backorder |
| 550781507710 | 4800 | 2700 | 2100 |
A7T57ZZ Seems like a simple Group By:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1MLcwBBLmhgZKOkogbGlgoBSrg1XK0Ai7nImFAYjCpc0MpCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SO - Doc & Line" = _t, #"Line Item Qty" = _t, #"OBD Line Item Qty" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO - Doc & Line", Int64.Type}, {"Line Item Qty", Int64.Type}, {"OBD Line Item Qty", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SO - Doc & Line"}, {{"Line Item QTY", each List.Sum([Line Item Qty]), type nullable number}, {"OBD Line Item Qty", each List.Sum([OBD Line Item Qty]), type nullable number}}) in #"Grouped Rows"Or in DAX:
Table = SUMMARIZE('Table'[SO - Doc & Line],"Line Item Qty",SUM([Line Item Qty]),"OBD Line Item Qty",SUM([OBD Line Item Qty]))
6 Replies
A7T57ZZ Seems like a simple Group By:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1MLcwBBLmhgZKOkogbGlgoBSrg1XK0Ai7nImFAYjCpc0MpCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SO - Doc & Line" = _t, #"Line Item Qty" = _t, #"OBD Line Item Qty" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO - Doc & Line", Int64.Type}, {"Line Item Qty", Int64.Type}, {"OBD Line Item Qty", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SO - Doc & Line"}, {{"Line Item QTY", each List.Sum([Line Item Qty]), type nullable number}, {"OBD Line Item Qty", each List.Sum([OBD Line Item Qty]), type nullable number}}) in #"Grouped Rows"Or in DAX:
Table = SUMMARIZE('Table'[SO - Doc & Line],"Line Item Qty",SUM([Line Item Qty]),"OBD Line Item Qty",SUM([OBD Line Item Qty]))Thank you! I used the DAX Summarize that I have used many times, but think I was trying to over complicate what I was trying to accomplish. I have many other columns with unique values, but will have to use a COUNT or MIN/MAX to display how I want them.
This is just one of 30k different orders that I need to display. I want the Sum for the two columns by based on the first column. Essentially it will take 50k lines down to the 30k indivudual orders and allow me to calculate the total qty of shipped and the remaining qty to ship. Apologies, I should have been more clear.
