2 Replies Latest reply on Nov 3, 2016 8:28 AM by Josh Delekta

    Budgeting and Variance Calculation Question

    Josh Delekta

      Hello,

       

      Please see attached. I have created two calculations for "Expense Variance" and "% of Variance" which works perfectly if there are values available for both "Expense Budget" and "Expense Actuals". Is there a way to update the calculations to say if there isn't a value for "Expense Budget" or "Expense Actuals" then the value should be "0"?

       

      In the attached example and screen shot below the expected value for project IT_04 would be (3,800,000) and -100%. The data source is automatically being updated so there isn't a way for me to provide a zero in the date for "Expense Actuals".

       

      Thank you!

       

        • 1. Re: Budgeting and Variance Calculation Question
          Michael Hesser

          Hi Josh;

           

          I think you can do this quite easily by checking if either of the values are null.

           

          I modified your % of Variance equation to this:

           

          % of Variance

          if isnull(sum([Expense Budget])) THEN 0

          ELSEIF isnull(SUM([Expense Actuals].[Amount])) then 0 //This may need to be -1

          ELSE ([Expense Variance])/SUM([Expense Budget])

          END

           

          You can use the IFNULL() function to populate values for [Expense Variance] in a similar, but perhaps sleeker,  manner:

           

          Expense Variance

          (ifnull(SUM([Expense Actuals].[Amount]),0) - ifnull(SUM([Expense Budget]),0))

           

          It appeared to work, but give it a spin!  --Michael

           

          --EDIT--

          Per your description, you may need to change the second line in your code from 0 to -1 (or -100%)

          1 of 1 people found this helpful
          • 2. Re: Budgeting and Variance Calculation Question
            Josh Delekta

            Michael,

             

            This worked perfect. Thank you!