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

# Budgeting and Variance Calculation Question

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

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

Michael,

This worked perfect. Thank you!