Sumif based on 2 columns

[Anonymous]

Hi All, 

I have a table something ilike this:

Id1	Id2	Value1	Value2
a	a	1	7
a	b	2	8
a	a	3	9
b	b	4	10
b	a	5	11
b	b	6	12

...and need to calculate this

Id	Value
a	=sum of Value1 where Id1 = a + sum of Value2 where Id2 = a
b	=sum of Value1 where Id1 = b + sum of Value2 where Id2 = b

That is, for any Id = 'x', I need the sum of Value1 + sum of Value2 where Id1 or Id2 is 'x'. I need it as a measure, but I couldn't figure how to do this efficiently, without practivally duplicating the table.

I'd be grateful for any help!

[LivioLanzo · Accepted solution]

Hello @Anonymous 

the best way is to apply this transofmration within Power query and then drop the id in the matrix rows and the sum of value in the matrix values:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSAWNDIDZXitWBiCQBsREQW8BFQNgYiC3BIklQNSYgnQZwIZAiU5CQIYoqM5CQkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id1 = _t, Id2 = _t, Value1 = _t, Value2 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Id1", type text}, {"Id2", type text}, {"Value1", Int64.Type}, {"Value2", Int64.Type}}),

    ReshapeTable = Table.Combine(
                            {
                                Table.SelectColumns(ChangedType, {"Id1", "Value1"}),
                                Table.RenameColumns(Table.SelectColumns(ChangedType, {"Id2", "Value2"}), {{"Id2", "Id1"}, {"Value2", "Value1"}})
                            })
in
    ReshapeTable

[Anonymous]

Yup, that's plain and easy, should've thought about it, but I did not consider doing this in PQ! Thanks a lot!