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

      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?