Forum Discussion
calculate new collumn in M-code
Hi All,
I have following table in PBI, in power query.
I need to find a way how to fill in Netto column.
Netto column should be: Sum of Bill - Sum of PDV per same order
So Im expecting only 2 numbers in Netto column as I have only 2 orders
Whats the best way to calculate it in Mcode or DAX, please?
If I add calculated column (Netto = Bill - PDV), it will calculate per each line, meaning if I will do SUM of Netto, results will be incorrect.
And I dont want to remove duplicates from Order collumns
| Order | PDV | Bill | Netto |
| 1/P1/10 | 2.93 | € 25.50 | |
| 1/P1/10 | 2.93 | € 25.50 | |
| 1/P1/10 | 2.93 | € 25.50 | |
| 2/P1/10 | 2.36 | € 18.80 | |
| 2/P1/10 | 2.36 | € 18.80 | |
| 2/P1/10 | 2.36 | € 18.80 | |
| 2/P1/10 | 2.36 | € 18.80 |
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:
9 Replies
- rajendraongole1
Super User
Hi Anonymous - you can achieve this either in Power Query (M code) or DAX
I have attached pbix file, withboth approaches, suggest to use Mcode is best approach.
Hope this helps.
Hi rajendraongole1 ,
thanks, but its not what I actually need.
I would like to achieve this - calucaluted Netto but only one line per order, because then I need to do SUM of Netto
- rajendraongole1
Hi , i forget to add group by
please refere the latest pbix .
anyway i have created measure also in my previous file generating one order for each. please check that solution too.
attaching power query editor solution too.
Hope this helps.
- danextian
Hi 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 asNetto = 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:
thanks danextian
thats what I need. Trying to replicate your code,but getting some errors.
Can u share pbix file maybe, please?
- danextian
Go to get data, search for blank query, go to the advanced editor, delete everything and paste the code. It should work just fine.
