Mulitple conditions for if clause

[peisenrot]

Hello,

I have a Bar chart where I want to show the dimensions only if it has two conditions (field MakeBuy should be 'make' and the calculated value should be ave 0.2

=if((MakeBuy='make'and Sum({<Source={'AccountStructureSOW'}>} Value)/
Sum({<Unit={'Costs'}>}Value)>'0.2'),SubCompetency)

the formular is shown as OK but the dimension is not guilty. what did i wrong?

Best regards

Pia

[marcus_sommer · Accepted solution]

Your condition contains an aggregation which aren't allowed within a dimension unless they are wrapped with an aggr(). This means your calculated dimension should be look rather more so:

aggr(
=if((MakeBuy='make'and Sum({<Source={'AccountStructureSOW'}>} Value)/
Sum({<Unit={'Costs'}>}Value)>'0.2'),SubCompetency),
Dim1, Dim2)

whereby Dim1 and Dim2 are just placeholder for the dimensionally context in which this condition should be evaluated - often it are the dimensions which are used within the chart but it could be others, too. In your case it's probably SubCompetency and maybe any period-field.

- Marcus