cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theitguy Regular Visitor
Regular Visitor

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_IDNameDepartmentCodeHire_Date
1John Doe1234501-11-2017
2Max Mustermann1234501-11-2017
3John Smith1234501-11-2017
4Jan Janssen1234501-11-2017
5John Blow12345601-11-2017
6Juan Pérez12345601-11-2017
7Matti Meikäläinen12345601-11-2017
8Jean Dupont9876501-11-2017
9John Brown1234501-12-2017
10John Browning12345

01-01-2018

 

 

 

Projects:

Project_IDNumber_Of_Tests
Project160
Project220
Project330

 

Projects_Relation:

Participant_IDProject_ID
1Project1
1Project2
1Project3
2Project1
2Project2
6Project1
7Project2

 

Departments:

DepartmentCodeOrganisationClusterDepartmentName
12345ABC MechatronicsSalesInHouseService
123456ABC MechatronicsSalesFieldService
98765ABC ElectronicsTechnologyDevelopment

 

Here is the outcome. I marked the desired outcome in red.

 

Unbenannt.png

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
waltheed
Advisor

Re: SUM is not calculated correctly

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])

 

Capture.PNG

 

 

Cheers, Edgar Walther
Power BI User Group Netherlands

View solution in original post

5 REPLIES 5
waltheed
Advisor

Re: SUM is not calculated correctly

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])

 

Capture.PNG

 

 

Cheers, Edgar Walther
Power BI User Group Netherlands

View solution in original post

theitguy Regular Visitor
Regular Visitor

Re: SUM is not calculated correctly

Hi Walther,

 

this does the trick, thank you!

Can you tell me why I need to edit the relationships and add the additional column?

waltheed
Advisor

Re: SUM is not calculated correctly

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. 

 

Cheers, Edgar Walther
Power BI User Group Netherlands
theitguy Regular Visitor
Regular Visitor

Re: SUM is not calculated correctly

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?

waltheed
Advisor

Re: SUM is not calculated correctly

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. 

 

Cheers, Edgar Walther
Power BI User Group Netherlands

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 249 members 2,693 guests
Please welcome our newest community members: