Forum Discussion
calculate new collumn in M-code
Honestly, we'd have provided an approach for that had you mentioned that you wanted to get just the difference for the first row and not get the sum of difference for each order.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQPMNQ3NFDSUTLSszQGUaZ6pkqxOuTJGCHJGJsBKUMLPQsqyBxaoABGKCpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, PDV = _t, Bill = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"PDV", type number}, {"Bill", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order"}, {{"Grouped", each _, type table [Order=nullable text, PDV=nullable number, Bill=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([Grouped], "Index", 1)), #"Expanded Added Index" = Table.ExpandTableColumn(#"Added Custom", "Added Index", {"PDV", "Bill", "Index"}, {"PDV", "Bill", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Grouped"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "Order", "PDV", "Bill"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"PDV", type number}, {"Bill", type number}, {"Index", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [Index] = 1 then [Bill] - [PDV] else null) in #"Added Custom1"Please see below:
Super UserHi Anonymous .
In the query editor, group all rows by Order column.
Create a custom column that accessed a PDV and Bill columns with Grouped as a list then sum it.
[Grouped][PDV] creates a list of PDV values filtered by the current order row. Grouped is the the column while PDV is the column within the table in Grouped. Use List.Sum to get the sum of all values.
To keep the duplicates, expand the Grouped column leaving out Order as it is already in the table.
Here's the M Code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQPMNQ3NFDSUTLSszQGUaZ6pkqxOuTJGCHJGJsBKUMLPQsqyBxaoABGKCpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, PDV = _t, Bill = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"PDV", type number}, {"Bill", type number}})
in
#"Changed Type"
In DAX, that would be as simple as
Netto =
CALCULATE (
SUM ( Netto[Bill] ) - SUM ( Netto[PDV] ),
ALLEXCEPT ( Netto, Netto[Order] )
)
Hi danextian
thanks for your detailed guide, but still doesnt solve my issue.
with your approach, you will calcaulte Netto per each line....however my point is to get the results only ONCE per ONE order. Reason for that is casue I want to SUM Netto collumn to get total netto (so SUM would be 22.57 + 16.44 = 39.01)
- danextian
Honestly, we'd have provided an approach for that had you mentioned that you wanted to get just the difference for the first row and not get the sum of difference for each order.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQPMNQ3NFDSUTLSszQGUaZ6pkqxOuTJGCHJGJsBKUMLPQsqyBxaoABGKCpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, PDV = _t, Bill = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"PDV", type number}, {"Bill", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order"}, {{"Grouped", each _, type table [Order=nullable text, PDV=nullable number, Bill=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([Grouped], "Index", 1)), #"Expanded Added Index" = Table.ExpandTableColumn(#"Added Custom", "Added Index", {"PDV", "Bill", "Index"}, {"PDV", "Bill", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Grouped"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "Order", "PDV", "Bill"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"PDV", type number}, {"Bill", type number}, {"Index", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [Index] = 1 then [Bill] - [PDV] else null) in #"Added Custom1"Please see below: