Forum Discussion

BobbyDollar's avatar
BobbyDollar
Frequent Visitor
7 years ago
Solved

Calculate with filter on multiple tables

Hi

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

 

There are 2 tables, TableResult and TableTarget

 

TableResult
RegionProfit_CenterRevenueRegion_Target
DEPC11001500
DEPC2100300
NLPC1200700
…......…

 

TableTarget
RegionProfit_CenterRevenue
DEPC11000
DEPC1500
DEPC2300
NLPC1700

 

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's avatar
    AlB
    7 years ago

    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  Datanaut

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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  Datanaut