Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BobbyDollar
Frequent Visitor

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?

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

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

View solution in original post

1 REPLY 1
AlB
Community Champion
Community Champion

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.