Help: Sum with multiple conditions

[Anonymous]

Hi,

I am trying to sum quantity based on strategy and movement shown in table below. For example, sum the quantity for movement 601 & 202 for each strategy. 

So Strategy AAD should be a total of -467 and 80; Srategy AAP should be a total of -573305 and 2200.

Any help would be appreciated. 

[jennratten]

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"

[Anonymous]

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.

[jennratten]

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.