Calculate sum values from multiple tables with filter

Hi all,

I'm lost at this moment. I've 8 tables (8 companies) calle ReportingBalance_Company1, ReportingBalance_Company2, et cetera.

The values in each table must be calculated per row, because I need the possibility to filter them later on. Now I've this:

AmountRevenue =

CALCULATE(SUM(tb_ReportingBalance_Company1[Amount PL]); FILTER(tb_GLAccounts_Company1; tb_GLAccounts_Company1[Type] = 110)) +

CALCULATE(SUM(tb_ReportingBalance_Company2[Amount PL]); FILTER(tb_GLAccounts_Company2; tb_GLAccounts_Company2[Type] = 110))

The result is the total sum of the values of each [Amount PL]. And with a filter at teh matrix I'm able to show the value of each Company.

But(!), when I add the same row for Company3, it brings me a complete wrong value. What is the beste solution / DAX formula to calculate the values of each Company and I can filter later on in a matrix?

Sorry, I don't have more information. Many thanks for your help!

Need Help

v-kelly-msft
6 years ago
Hi AltusTellus ，

I made a sample .pbix file according to your description and used the measure you provided,but it seems no problem,see below:

Before adding one row in company 3,it shows :

After adding one row valued 75,it shows:

My measure is as below:

Measure = CALCULATE(SUM(tb_ReportingBalance_Company1[Amount PL]),FILTER('tb_ReportingBalance_Company1','tb_ReportingBalance_Company1'[Type] = 110)) + CALCULATE(SUM('tb_GLAccounts_Company2'[Amount PL]),FILTER('tb_GLAccounts_Company2','tb_GLAccounts_Company2'[Type] = 110))+CALCULATE(SUM('tb_GLAccounts_Company3'[Amount PL]),FILTER('tb_GLAccounts_Company3','tb_GLAccounts_Company3'[Type]=110))

Pls check my .pbix file ,try to produce your senario and show me the error.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!
Calculate sum values from multiple tables with filter.pbix27 KB