cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Raul Member
Member

Sum column values based on another table

Hello,

I have this situation:

Mapa.JPG

 

TABLE2    
LineExpenseCodAreaDateOffice
110Area101/01/2017Office1
220Area302/01/2017Office1
315Area203/02/2017Office1
415Area204/01/2017Office1
520Area305/01/2017Office3
615Area106/03/2017Office2
730Area307/01/2017Office3
825Area208/04/2017Office2
910Area309/01/2017Office3
105Area110/06/2017Office1

 

I have a measure like this: TotalImport = SUM(TABLE1.Import)

 

And a matrix visualitation with this parameters:

Month/OfficeTotalImportTotalExpense
1  
   Office1100 
   Office2160 
   Office3120 
2  
   Office1200 
   Office2150 
   Office360 
3  
   Office140 
   Office270 
   Office3120 


And this filters: YEAR = 2017, MONTH = 1 to 3, AREA = Area1 into a slicers. 

 

I need to calculate the sum of the expense values in TABLE2 (TotalExpense) for this filters and put the total into the matrix, to obtain the result in this case, like this:

Month/OfficeTotalImportTotalExpense
1  
   Office110025
   Office2160 
   Office3120 
2  
   Office1200 
   Office2150 
  Office360 
3  
   Office140 
   Office27015
   Office3120 


I create the measure TotalExpense = CALCULATE(SUM(TABLE2[Expense]);ALLSELECTED(Areas[Area])) but the result is not correct. What is the correct DAX formula? 

 

Thank you very much.

1 ACCEPTED SOLUTION

Accepted Solutions
srinivt Regular Visitor
Regular Visitor

Re: Sum column values based on another table

It is because of relationships are not appropriately defined in your model. Looking at your model, it seems like you need to make the following changes: 

- Change Relationship between Table1 and Calendar to be Many-to-One and Single-Directional. (Note the Many should be from Table1). 

- Change Relationship between Table2 and Calendar to be Many-to-One and Single-Directional. Then ensure that the relationship is active. (Again note that Table2 should be many side.)

- Create new relationship between Table2[Office] and Offices[Office] as Many-To-One and Single-Directional.

- Change the measure TotalExpense = SUM(Table2[Expense]). It is possible that I misunderstand the requirement for this measure. But it is good to start simple and then define a more complex measure if needed.

 

I tried these changes and seem to be getting the correct numbers, but you should verify if that is what you want.  

View solution in original post

9 REPLIES 9
Moderator Eric_Zhang
Moderator

Re: Sum column values based on another table

@Raul

Is the AREA filter from the AREAS table? You can try a measure like

 

TotalExpense =
CALCULATE (
    SUM ( TABLE2[Expense] ),
    FILTER ( TABLE2, TABLE2[AREA] = MAX ( AREAS[Area] ) )
)
Raul Member
Member

Re: Sum column values based on another table

Thanks @Eric_Zhang for your help.

Answering your question: yes, the AREA filter is from the Area table.

I have aplied your formula but the result in not correct. I obtein the same result that the TotalImport column. Any other idea?

 

Super User I
Super User I

Re: Sum column values based on another table

I need some clarification. Do Offices and Areas have a relationship?

Also, why is AREAS joined to table1 and not table2?



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Moderator Eric_Zhang
Moderator

Re: Sum column values based on another table


@Raul wrote:

Thanks @Eric_Zhang for your help.

Answering your question: yes, the AREA filter is from the Area table.

I have aplied your formula but the result in not correct. I obtein the same result that the TotalImport column. Any other idea?

 


@Raul

It is hard to know why not correct without seeing the whole picture. Is it possible to upload the pbix file? You can upload it to Onedrive/Google drive and share the link in this thread or via a private message. Do remember to mask sensitive data before uploading.

Raul Member
Member

Re: Sum column values based on another table

I have make some changes on the data model and the TotalExpense DAX formula. I relationed the AREAS table with TABLE2 and the result in the matrix table is correct but not quite: the TotalExpense columns shows the correct result but repeat the same value for every Office on every month, like this:

Month/OfficeTotalImportTotalExpense
1  25
   Office110025
   Office2160 25
   Office312025 
2  0
   Office1200 0
   Office2150 0
  Office360 0
3  15
   Office140 15
   Office27015
   Office3120 15

 

The TotalExpense formula is this:  

TotalExpense =
CALCULATE (
    SUM ( TABLE2[Expense] ),
    FILTER ( TABLE2, TABLE2[AREA] = VALUES( AREAS[Area] ) )
)

 Any suggestion? Thank you.

Raul Member
Member

Re: Sum column values based on another table

With this changes, if I put the TABLE2.Expense field into the matrix, it shows the same result:

 

Month/OfficeTotalImportExpense
1  25
   Office110025
   Office2160 25
   Office312025 
2  0
   Office1200 0
   Office2150 0
  Office360 0
3  15
   Office140 15
   Office27015
   Office3120 15
Raul Member
Member

Re: Sum column values based on another table

I attach de pbix file with some new changes on the data model. I continued without finding the correct formula for the total.

Could you help me, please? Thank you.

 

https://drive.google.com/file/d/1yK9tSOUpzUxXAmIvofDIUin3SvAXjls0/view?usp=sharing

 

 

srinivt Regular Visitor
Regular Visitor

Re: Sum column values based on another table

It is because of relationships are not appropriately defined in your model. Looking at your model, it seems like you need to make the following changes: 

- Change Relationship between Table1 and Calendar to be Many-to-One and Single-Directional. (Note the Many should be from Table1). 

- Change Relationship between Table2 and Calendar to be Many-to-One and Single-Directional. Then ensure that the relationship is active. (Again note that Table2 should be many side.)

- Create new relationship between Table2[Office] and Offices[Office] as Many-To-One and Single-Directional.

- Change the measure TotalExpense = SUM(Table2[Expense]). It is possible that I misunderstand the requirement for this measure. But it is good to start simple and then define a more complex measure if needed.

 

I tried these changes and seem to be getting the correct numbers, but you should verify if that is what you want.  

View solution in original post

Raul Member
Member

Re: Sum column values based on another table

Great!! It works!

I've made your relations changes and works fine. But I've needed the measure TotalExpense to calculate the right values:
TotalExpense = CALCULATE(SUM (TABLE2[Expense]);FILTER(TABLE2;TABLE2[CodArea]=VALUES(TABLE1[CodArea])))

Thank you very much!!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors