Forum Discussion

theitguy's avatar
theitguy
Helper I
8 years ago
Solved

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 n...
Need Help
  • waltheed's avatar
    waltheed
    8 years ago

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

     

     

     

waltheed's avatar
waltheed
Impactful Individual
8 years ago

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. 

 

theitguy's avatar
theitguy
Helper I
8 years ago

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's avatar
    waltheed
    Impactful Individual
    8 years ago

    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.