Subsctracting two row values from eachother and entering the result in a new row - In The same Table

[MacJasem]

Hi Guys

I really need your help on this one!

What I need help with is; how do i add a new row that takes the Sum_Amount values for the No column rows A100 and A200 and substract them from eachother and enters the result in a new Sum_Amount row with a new No row called B and Accoun_No 2?

[v-jingzhang]

Hi @MacJasem 

Does every date has only two rows and you always want to substract the second row from the first row? If so, you can try the following way:

= Table.Group(#"Changed Type", {"Date"}, {{"Subtraction", each Table.First(_)[Sum_Amount] - Table.Last(_)[Sum_Amount]}})

Based on above, to append a new row to each group and get the expected result, you can try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNTTQNTIwNFLSUdI1NDMwMAAyHA3BlIFSrA6aEkuoAiMwZagUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sum_Amount = _t, No = _t, Account_No = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sum_Amount", Int64.Type}, {"No", type text}, {"Account_No", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Subtraction", each Table.Combine({_, #table({"Date", "Sum_Amount", "No", "Account_No"},{{List.Max(_[Date]),Table.First(_)[Sum_Amount] - Table.Last(_)[Sum_Amount],"B",2}})})}}),
    #"Expanded Subtraction" = Table.ExpandTableColumn(#"Grouped Rows", "Subtraction", {"Sum_Amount", "No", "Account_No"}, {"Sum_Amount", "No", "Account_No"})
in
    #"Expanded Subtraction"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[MacJasem]

Thanks @v-jingzhang 

Very good advice, but i've got an issue with this approach, because if i.e. Account_No "1" has a negative number and it has to be subtracted from the "0" Account_No, then the subtraction becomes an addittion in this case. 

Any advice on a work-around?

[v-jingzhang]

Hi @MacJasem 

Have you solved this problem? Is there any update?

[MacJasem]

Hi @v-jingzhang 

I haven't solved it yet. 

My case is with regards to Financial data, and the grouping of rows and at the same time calculating Sub totals for those financial entries. The system has reversed the values, so that income data is mostly negative and costs are mostly positive, and when these are grouped and calculating contribution margin, EBITDA, EBIT etc the numbers are shown in negative where they should be positive. The straight forward way of changing these is eazy by i have a whole lot of calculations that tally these different P/L post into groups that is used to calculate the abovementioned values in the P/L Statement.

I don't know if that makes it harder or easier to understand?  

[v-jingzhang]

Hi @MacJasem 

My current solution always subtracts the second row's value from the first row's value without considering whether a number is positive or negative. So if the second value is negative, you want the result to be an addition or still a substraction? Can you provide some more examples? I listed some possible scenarios as below, you may enter the expected results for ? values. 

Sum_Amount	Account_No
-100	0
50	1
?	2

Sum_Amount	Account_No
-100	0
-200	1
?	2

Sum_Amount	Account_No
100	0
-50	1
?	2

Sum_Amount	Account_No
100	0
50	1
?	2

[MacJasem]

Isn't there a similar way to group values but instead of summarizing it just substract?
= Table.Group(#"Filtered Rows", {"Date"}, {{"sum amount", each List.Sum([sum amount]), type nullable number}})