Help with Formula to calculate total with conditions

[heij1421]

Attached you find this dashboard

In the last graph I need an additional column. The calculation should be the sum of the red box in case of the first line.

This is the total amount of 'Team Cor' except where 'team cor' (linked to cost center of a project) <> team cor (linked to cost center of an employee).

How do I do that?

[jonathandienst]

I suggest that you reduce the data in your upload. I will not open 65MB file to see if can help you.

Preparing examples for Upload - Reduction and Data Scrambling

[heij1421]

Jonathan, I already reduced the data using the 'reduce data - keep possible values' option.

[jonathandienst]

OK, but I suggest that you reduce it still further...

[heij1421]

I figured it out myself.

Solution:

=SUM(AGGR(IF([Projects CostCenterDescription]=[Employee CostCenterLabel],SUM(Total <[Employee CostCenterLabel]>[Projectactuals RealQuantityCosts] * [Item Sales Package Price])-SUM([Projectactuals RealQuantityCosts] * [Item Sales Package Price])),[Projects CostCenterDescription], [Employee CostCenterLabel]))

[heij1421]

I figured it out myself.

Solution:

=SUM(AGGR(IF([Projects CostCenterDescription]=[Employee CostCenterLabel],SUM(Total <[Employee CostCenterLabel]>[Projectactuals RealQuantityCosts] * [Item Sales Package Price])-SUM([Projectactuals RealQuantityCosts] * [Item Sales Package Price])),[Projects CostCenterDescription], [Employee CostCenterLabel]))