cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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?
 
Sorry, I don't have more information. Many thanks for your help!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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 :

 

Annotation 2020-09-07 151124.png

After adding one row valued 75,it shows:

Annotation 2020-09-07 151239.png

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!

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

@AltusTellus , this seems correct.

 

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

https://radacad.com/append-vs-merge-in-power-bi-and-power-query



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

@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

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


Connect on LinkedIn
Highlighted

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 :

 

Annotation 2020-09-07 151124.png

After adding one row valued 75,it shows:

Annotation 2020-09-07 151239.png

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!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors