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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
AltusTellus
Helper III
Helper III

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

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
amitchandak
Super User
Super User

@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

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

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!

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

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. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.