Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
theitguy
Helper I
Helper I

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
waltheed
Solution Supplier
Solution Supplier

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
ITsmart BI and Analytics consultant

View solution in original post

5 REPLIES 5
waltheed
Solution Supplier
Solution Supplier

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
ITsmart BI and Analytics consultant

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. 

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

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. 

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.