Calculate with filter on multiple tables

BobbyDollar — Fri, 06 Sep 2019 12:51:24 GMT

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?

Re: Calculate with filter on multiple tables

AlB — Fri, 06 Sep 2019 16:10:00 GMT

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

topic Calculate with filter on multiple tables in DAX Commands and Tips

Calculate with filter on multiple tables

Re: Calculate with filter on multiple tables