Query Editor M new custom column: if statement that subtracts sum of other rows from one row

[alicek]

Hi! I have a bit of a complex M question for you all. How would I create a new column that, for one row with something in column A, subtracts the values in all the other rows from Column from the value for that row in Column B?

End desired state:

Organization	Amount	True Amount -- new custom column
A	10,000	7,000
B	1,000	1,000
C	1,000	1,000
D	1,000	1,000

I've tried something like this:

if [Organization] = "A"

    then Value.Subtract(
     (Table.SelectRows(each [Organization] = "A")[Issued]),
     List.Sum(Table.SelectRows(each [Organization] <> "A")[Issued])

     )

else [Issued]

In the new column that code creates, that correctly fills in the rows with the same numbers in Amount for Organizations B, C, and D, but it throws an error for the cell for ORganization A, which is what I'm aiming to get at. 

Does anyone have an improvement to the code?

[PhilipTreacy · Accepted solution]

Hi @alicek 

Download this sample PBIX file with the code below

This works for me

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0AAKlWJ1oJScoD8xxRua4wDmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Organization = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Organization] = "A" then [Amount] - List.Sum(List.RemoveRange(Table.Column(#"Added Index", "Amount"),[Index],1)) else [Amount])
in
    #"Added Custom"

regards

Phil