cancel
Showing results for
Did you mean:
Member

## Sum column values based on another table

Hello,

I have this situation:

 TABLE2 Line Expense CodArea Date Office 1 10 Area1 01/01/2017 Office1 2 20 Area3 02/01/2017 Office1 3 15 Area2 03/02/2017 Office1 4 15 Area2 04/01/2017 Office1 5 20 Area3 05/01/2017 Office3 6 15 Area1 06/03/2017 Office2 7 30 Area3 07/01/2017 Office3 8 25 Area2 08/04/2017 Office2 9 10 Area3 09/01/2017 Office3 10 5 Area1 10/06/2017 Office1

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

And a matrix visualitation with this parameters:

 Month/Office TotalImport TotalExpense 1 Office1 100 Office2 160 Office3 120 2 Office1 200 Office2 150 Office3 60 3 Office1 40 Office2 70 Office3 120

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/Office TotalImport TotalExpense 1 Office1 100 25 Office2 160 Office3 120 2 Office1 200 Office2 150 Office3 60 3 Office1 40 Office2 70 15 Office3 120

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
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.

9 REPLIES 9
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] ) )
)
```
Member

## Re: Sum column values based on another table

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

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

Moderator

## Re: Sum column values based on another table

@Raul wrote:

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.

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/Office TotalImport TotalExpense 1 25 Office1 100 25 Office2 160 25 Office3 120 25 2 0 Office1 200 0 Office2 150 0 Office3 60 0 3 15 Office1 40 15 Office2 70 15 Office3 120 15

The TotalExpense formula is this:

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

Any suggestion? Thank you.

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/Office TotalImport Expense 1 25 Office1 100 25 Office2 160 25 Office3 120 25 2 0 Office1 200 0 Office2 150 0 Office3 60 0 3 15 Office1 40 15 Office2 70 15 Office3 120 15
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.

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.

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!!

Announcements

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

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors