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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Dutch
Regular Visitor

Calculate sum from another table based on 2 of 3 linked items

I have 2 data tables that are linked through 3 master data tables.

 

Table one:

Period - Company Code - Profit Center - Sales Amount

 

Table two:

Period - Company Code - Profit Center - Net assets amount

 

I like to add the summary sales from table one to table 2, but it should only match for period and company code and ignore the link for Profit center.

 

I tried with calculate(sum(table1[sales amount]),allexcept(table2[Period],table2[Company code]))

 

but this doesn't work.

 

Any ideas?

1 ACCEPTED SOLUTION

Figured it out:

Thank you

 

=
CALCULATE (
    SUM ( Table1[Sales Amount] ),
    FILTER (
        ALL ( Table1),
        Table1[Company Code] = Table2[Company Code]
    ),
    FILTER ( ALL ( Table1), Table1[Period] = Table2[Period] )
)

View solution in original post

3 REPLIES 3
Stachu
Community Champion
Community Champion

try this as a calculated column in Table2

=
CALCULATE (
    SUM ( Table1[Sales Amount] ),
    FILTER (
        ALL ( Table1[Company Code] ),
        Table1[Company Code] = Table2[Company Code]
    ),
    FILTER ( ALL ( Table1[Period] ), Table1[Period] = Table2[Period] )
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Figured it out:

Thank you

 

=
CALCULATE (
    SUM ( Table1[Sales Amount] ),
    FILTER (
        ALL ( Table1),
        Table1[Company Code] = Table2[Company Code]
    ),
    FILTER ( ALL ( Table1), Table1[Period] = Table2[Period] )
)

Thank you, but it didn't work.

It didn't ignore the link on the profit center. If I would have 5 rows with different profit centers for a specific period and a specific company code, I want all 5 lines to get the same answer. In table 1 there is more than one row for a specific period and a specific company code. Those need to be added together and put on all 5 lines in table 2.

 

 

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.