Sum of two columns in two different tables

[amiller5]

I have two tables - one with historical values related to stages - this one is filtered to these specific stages.

And then I have another table from a different source with the accepted and declined stages.

I need to create a measure that combines the total filtered amount from the 1st table and only the "Closed accepted" from the second.

I have tried summing the two separately and the number is not coming out correctly (see "Total pipeline (2)") - even with the correct filters.

total historical amount = SUM('Sales 4'[Historical Amount])

Closed accepted value = CALCULATE(SUM('Sales 1'[Amount]),FILTER('Sales 1','Sales 1'[Stage]="Closed - Accepted"))

Total pipeline (2) = 'Sales 4'[total historical amount]+'Sales 1'[Closed accepted value]

Do I need to also filter the historical amount in the measure to get the correct value?

[Anonymous · Accepted solution]

Hi @amiller5 ,

It seems that you apply visual-level filter on 1st table visual, and it will only effect the data display of current visual... So you need to update the formula of measure [total historical amount] as below with the filtered options in Filters pane.

total historical amount =
CALCULATE (
    SUM ( 'Sales 4'[Historical Amount] ),
    FILTER (
        'Sales 4',
        'Sales 4'[Historical Stage]
            IN {
            "Opportunity conxxx",
            "Use Case Demo",
            "Business Negotiation",
            "Contract Submitted",
            "Legal Review"
        }
    )
)

Or you can apply the field [Historical Stage] of the historical table to create one slicer to filter 1st table visual and replace the original visual-level filter. Then you no need to the formula of measure [total historical amount]...

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards