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

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

    Anat Dagan


      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?