5 Replies Latest reply: Apr 19, 2017 4:13 AM by omar bensalem

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

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?

• ###### Re: How to handle zeros when Calculating % of difference between Esitmate and Actual Cost?

Try sthing like this,

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

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

/

SUM([Estimated Cost])

• ###### Re: How to handle zeros when Calculating % of difference between Esitmate and Actual Cost?

try:

alt(

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

/

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

1

)

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

• ###### Re: How to handle zeros when Calculating % of difference between Esitmate and Actual Cost?

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?

• ###### Re: How to handle zeros when Calculating % of difference between Esitmate and Actual Cost?

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

)

)

• ###### Re: How to handle zeros when Calculating % of difference between Esitmate and Actual Cost?

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])