Forum Discussion
Help: Sum with multiple conditions
Hello - you could filter the table for the Movements to be summed, then group the table and sum to Quantity and Cost.
BEFORE
AFTER
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcw7CsAgEIThu2xtYFz3YSvkAOnF+19DiSZsM8UH8/dOrd2UKCOvNVGlkT60Fy8xD8jgtRWHnv9cDdWDnrd6KdDgKmU7JEZ2lxkLxwQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Strategy = _t, Movement = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Strategy", type text}, {"Movement", Int64.Type}, {"Quantity", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Movement] = 202 or [Movement] = 601)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Strategy"}, {{"Quantity Sum", each List.Sum([Quantity]), type nullable number}})
in
#"Grouped Rows"
Thanks jennratten. Because I have a list of movement calculations and also strategy may not have all of the movements. For example, I would need to calculate movement 601+981, and 101 for each strategy shown below.
Strategy Movement Quantity AAD 601 1 AAD 981 2 AAP 601 3 AAP 981 4 AAP 101 5 So, it should be
Strategy Movement Quantity AAD 601/981 3 AAP 601/981 7 AAD 101 0 AAD 101 5 AAD doesn't have 101 movement so it should be zero, and would need to print all different movement calculations. I am thinking two loops - not sure if Power Query could do that. Open to another simplified way.
Based on this info, I think it would be best to create a mapping table that shows how the Movements should be grouped. Since group should be summed for each Strategy we can omit Strategy from the mapping table. Something like the snip below, where each Movement is listed along with a group number. All Movements with the same group number will be added together.