calculate new collumn in M-code

[martysk]

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

[martysk]

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.

[danextian]

Hi @martysk .

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] )
)

[martysk]

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 · Accepted solution]

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:

[rajendraongole1]

Hi @martysk  - 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.

[martysk]

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.

[martysk]

thanks @rajendraongole1 , but I need to keep all the rows and collumns.

In my file I have much more collumns and rows which I need to keep, Ive just provided collumns which are related to my issue.

When I use group by, it will remove other collumns which I dont want to.

Also, I dont want to remove duplicates rows, as I need all the rows.

I literally needs to achieve this state as picture bellow, just dont know how or if its even possible