cancel
Showing results for
Did you mean:
Highlighted
Helper II

## 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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
5 REPLIES 5
Highlighted
Super User IV

@AltusTellus , this seems correct.

The other way would be that you append the tables into one table.

Proud to be a Super User!

Highlighted
Helper II

Hi, thnx for your reply. But when I add an third row, I don't get the correct values, why?

P.S. Your suggestion for 1 table is my second option.

Highlighted
Helper II

@amitchandak I've the 8 tables appended to 1 new table. How can I filter with another table (like I wrote above)? Many thnx.

Highlighted
Super User II

Before you append the tables, make sure that you have a company column in each table specifying which company the rows are related to. When you have this you should be able to use that column as a column/row dimension or a filter in a slicer.

Br,
J

Highlighted
Community Support

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors