Forum Discussion
SUM is not calculated correctly
Hello guys,
I want to calculate the sum of some value for my participants. But I am doing something wrong, even it is pretty simple.
Each participant has assigned projects. Each projects has a number of tests. I would like to calculate the sum for each participant. And afterwards for every participant group. But the totals and the group sums are incorrect. Only the overall sum of projects' tests is count.
Here is the data:
Participants:
| Participant_ID | Name | DepartmentCode | Hire_Date |
| 1 | John Doe | 12345 | 01-11-2017 |
| 2 | Max Mustermann | 12345 | 01-11-2017 |
| 3 | John Smith | 12345 | 01-11-2017 |
| 4 | Jan Janssen | 12345 | 01-11-2017 |
| 5 | John Blow | 123456 | 01-11-2017 |
| 6 | Juan Pérez | 123456 | 01-11-2017 |
| 7 | Matti Meikäläinen | 123456 | 01-11-2017 |
| 8 | Jean Dupont | 98765 | 01-11-2017 |
| 9 | John Brown | 12345 | 01-12-2017 |
| 10 | John Browning | 12345 | 01-01-2018
|
Projects:
| Project_ID | Number_Of_Tests |
| Project1 | 60 |
| Project2 | 20 |
| Project3 | 30 |
Projects_Relation:
| Participant_ID | Project_ID |
| 1 | Project1 |
| 1 | Project2 |
| 1 | Project3 |
| 2 | Project1 |
| 2 | Project2 |
| 6 | Project1 |
| 7 | Project2 |
Departments:
| DepartmentCode | Organisation | Cluster | DepartmentName |
| 12345 | ABC Mechatronics | Sales | InHouseService |
| 123456 | ABC Mechatronics | Sales | FieldService |
| 98765 | ABC Electronics | Technology | Development |
Here is the outcome. I marked the desired outcome in red.
Thank you!
Hi itguy,
This should do the trick:
Change all the relationships to just single direction filter.
Add a calculated column to your projects-relation table, like this:
Nr of Tests = RELATED(Projects[Number_Of_Tests])
5 Replies
Hi itguy,
This should do the trick:
Change all the relationships to just single direction filter.
Add a calculated column to your projects-relation table, like this:
Nr of Tests = RELATED(Projects[Number_Of_Tests])
Hi Walther,
this does the trick, thank you!
Can you tell me why I need to edit the relationships and add the additional column?
Always keep cross filter direction single, unless you really need to. Otherwise it can lead to unpredictable behaviour, and you do not need it for this scenario.
But in order to be able to filter the number of tests by participant and department, you need to have the number in the projects-relation table. otherwise the filter cannot reach the number of tests. Your calculation happens to be pretty straighforward, but there are many to many scenarios that require more complex calculations and relationships.
