Calculate sum of values calculated from an if statement

[Anonymous]

Hi,

I have to calculate a certain value for each [Equipamento-Num. SAP] on my data set using the following if cycle depending on the number of occurrences of [Ordem] for when Avaria={1}:

If (IsNull(Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])) or Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])=0,

  (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6)))),

    (Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} All Data)-MakeDate(2008, 01, 01))*24/pow(10,6)))))

Then I also have to place what is above inside another cycle for when Avaria={0}:

If (Avaria=1,

  ##The Code above##,

  (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6))))

)

This  works fine on a table for when I have the results by [Nome Equipamento]. However I want to have the average of all these values on a certain year. And when I change the table's dimension to years and apply the following expression the results I get do not resemble the results I should have:

Sum(If (Avaria=1,

  If (IsNull(Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])) or Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])=0,

  (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6)))),

    (Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} All Data)-MakeDate(2008, 01, 01))*24/pow(10,6))))),

  (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6))))

))

How can I get the correct sum of the values I calculate through an if statement?

Thank you,

[sunny_talwar]

May be this

Sum(Aggr(

If(Avaria=1,

  If (IsNull(Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])) or Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])=0,

  (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6)))),

    (Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} All Data)-MakeDate(2008, 01, 01))*24/pow(10,6))))),

  (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6))))

)

, [Nome Equipamento], Year))