Calculate with filter on multiple tables

[BobbyDollar]

Hi

Novice asking again beginner questions … Thanks in advance for your help

There are 2 tables, TableResult and TableTarget

TableResult
Region	Profit_Center	Revenue	Region_Target
DE	PC1	100	1500
DE	PC2	100	300
NL	PC1	200	700
…	...	...	…

TableTarget
Region	Profit_Center	Revenue
DE	PC1	1000
DE	PC1	500
DE	PC2	300
NL	PC1	700

Scenario:

Column Region_Target in TableResult needs to be calculated, using 2 tables.

Region_Target = Sum of Revenue in TableTarget with Filter using Profit_Center and Region from TableResult.

Following would give me the correct value for Region_Target in row1:

CALCULATE(SUM(TableTarget[Revenue]);FILTER(TableTarget;TableTarget[Region]="DE";TableTarget[Profit_Center]="PC1"))

But obviously I want to refernce to Profit_Center and Region in TableResult to calculate automatically for each row.

I tried with SUMX and CALCULATETABLE but did not come to any useful result. An ALLEPECXT needs to go in there as well I guess.

Any advice?

[AlB · Accepted solution]

Hi @BobbyDollar 

Region_Target =
CALCULATE (
    SUM ( TableTarget[Revenue] );
    FILTER (
        TableTarget;
        TableTarget[Region] = TableResult[Region]
            && TableTarget[Profit_Center] = TableResult[Profit_Center]
    )
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers