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

    Budgeting and Variance Calculation Question

    Josh Delekta



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



          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



          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



            This worked perfect. Thank you!