1 of 1 people found this helpful
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:
(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%)
This worked perfect. Thank you!