This content has been marked as final.
Show 2 replies

1. Re: Budgeting and Variance Calculation Question
Michael Hesser Nov 3, 2016 8:29 AM (in response to Josh Delekta)1 of 1 people found this helpfulHi 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%)

2. Re: Budgeting and Variance Calculation Question
Josh Delekta Nov 3, 2016 8:28 AM (in response to Michael Hesser)Michael,
This worked perfect. Thank you!