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,
