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.
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!
Solved! Go to Solution.
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 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.
Hi Walther @waltheed
I see, thank you for explaining.
But, I just realized that changing the filter direction to single, does mean that I will not be able to reach a scenario where I can filter for Projects, will I?
Yes, that will still work.
If you filter by project, the project-relations table is filtered, and only the related participants (and departments) will be shown.
Also the other way around: If you filter by department, the participants are filtered, which filters the project-relations table, and the related projects are shown.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |