powerbidaxdata-analysispowerbi-desktopmeasure

Sum Values Which Appear In Both Tables


I have 2 related tables and I would like to sum if id number is exsist in the both tables.

table1   
id sum
1  5
3  10
5  15

table2   
id sum
1  10
2  20
5  30

Expected Answer

 sum = 5 + 10 + 15 + 30

Solution

  • Measure = 
    VAR a = INTERSECT(VALUES(table1[id]), VALUES(table2[id]))
    VAR b = CALCULATE(SUM(table1[sum]), table1[id] IN a)
    VAR c = CALCULATE(SUM(table2[sum]), table2[id] IN a)
    RETURN b+c