Forum Discussion
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.
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
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] ) ) )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 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?
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.
I need some clarification. Do Offices and Areas have a relationship?
Also, why is AREAS joined to table1 and not table2?
