Sum sales from 2 tables based on different criteria

[Anonymous]

Hello,

I have 2 tables and I would like the sum of total sales based a 1 specific condition.

Table 1 : total sales for all products except sum of sales for product a

Table 2 : total sales only for product a

How can I get the sum with the above condition?

I succeed to exclude sum of product a from table 1 but I don't know how to include those sales from Table 2 into table 1. Here is my formula as of now but it doesn't work

Total sales =
CALCULATE(
 SUM(table 1[sales 1]) + sum(table 2[sales 2])
 FILTER(table 1,table 1[product]<>"product a"))

Thank you for your help!

[Anonymous · Accepted solution]

Hello steve76

1.calculate measure for total sale for table 1 :

     total sale = CALCULATE(SUM(Sheet1[Sale] ),FILTER(Sheet1,Sheet1[Product Name]<>"prod a"))

 2. calculate measure for total sale for table 2: 

    total sale = CALCULATE(SUM(Sheet2[Sale]),FILTER(Sheet2,Sheet2[Product Name]="prod a")

3. simply sum both measure:

     tottal sale plus total sale = [total sale] + [total sale]

[Anonymous]

hello @Anonymous, @Stachu,

Thank you for your help, the measure for each table and sum is working fine.

[Stachu]

you almost got, try this (FILTER is not really necessary here)

Measure = 
CALCULATE(
 SUM(table 1[sales 1]),
 table 1[product]<>"product a")
) + 
CALCULATE(
 SUM(table 2[sales 2]),
 table 2[product]="product a")
)