How to handle zeros when Calculating % of difference between Esitmate and Actual Cost?

[Anonymous]

Hi,

I need to calculate the percentage of the difference between Estimated Price and Actual Cost.

My current calculation works fine as long as the estimated Price is >0:

This is the calculation i used for "% Difference from Est" column:

SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])

/

SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])

(the set analysis is there because I only wanted the calculation to work for items that were already purchased)

I know my calculation returns "-"  because Estimated Cost is zero but I don't know how to change it so that it returns 100% when the Estimated Cost is zero but the Actual Cost is positive. Can you please help me?

[OmarBenSalem]

Try sthing like this,

if(sum("Actual Cost")=0,1,

(SUM([Actual Cost])- SUM([Estimated Cost]))

/

SUM([Estimated Cost])

[Anonymous]

try:

alt(

SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])

/

SUM({$<[Actual Cost]={">0"}>}[Estimated Cost]),

1

)

---------------------------

[Anonymous]

Thanks Omar and Robin,

These options work great for cases where Actual Cost >0 and Estimated Cost = 0.

However they also show 100% for cases where both Actual Cost and Estimated Cost equal zero.

Before, such lines (where both values = 0) were suppressed because I unticked the "show zero values" but this change makes them reappear since I now have 100% against these lines.

Any other option?

[Anonymous]

if(

    not isnull(SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])/SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])),

    SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])/SUM({$<[Actual Cost]={">0"}>}[Estimated Cost]),

    if(

        (SUM({$<[Actual Cost]={">0"}>}[Actual Cost])=0) and ((SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])=0)),0,1

    )

)

[OmarBenSalem]

maybe

if(sum("Actual Cost")=0 and sum([Estimated Cost])=0,0,

if(sum("Actual Cost")=0 and sum([Estimated Cost])>0,1,

(SUM([Actual Cost])- SUM([Estimated Cost]))

/

SUM([Estimated Cost])