Forum Discussion
Query Editor M new custom column: if statement that subtracts sum of other rows from one row
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?
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
1 Reply
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