
Re: Variance Calculation
lakshmipathi p Aug 8, 2017 5:05 AM (in response to Alison Cooke)Hi,
Check this variance formula:
sum(Actual)sum(Budget)/Sum(Budget)
Or post sample data or Expr
Regards,

Re: Variance Calculation
Andrea Gigliotti Aug 8, 2017 5:15 AM (in response to lakshmipathi p)better like this:
( sum(Actual)sum(Budget) ) / Sum(Budget)
and format it as percentage.


Re: Variance Calculation
Rahul Pawar Aug 8, 2017 5:08 AM (in response to Alison Cooke)Could you please share the used expression for variance field? Also state the expected result for one of the Project Budget Item.
Generally, I followed below expression (It is purely considering business requirement at my end):
= ((Sum(Budget)  Sum(Actual))/Sum(Actual)) * 100
Regards!
Rahul Pawar

Re: Variance Calculation
Alison Cooke Aug 8, 2017 5:18 AM (in response to Alison Cooke)Hello Lakshmipathi and Rahul,
Sorry, I didn't post the expression. I have done so now.
For Back Office Administration Salaries (Integral) I am expecting 78,450 to be the result.
Using the calculation you have both suggested, which in my app looks like this:
(sum([Q1 Project Budget]) + sum([Q2 Project Budget]) + sum([Q3 Project Budget]) + sum([Q4 Project Budget])  sum([Project Expenditure Amount])
/ sum([Project Expenditure Amount]))
I get the following result, which is incorrect.
Is there anything wrong with the calculation? Or do you imagine that the error lies elsewhere?

Re: Variance Calculation
lakshmipathi p Aug 8, 2017 5:26 AM (in response to Alison Cooke)Hi,
Try this,
(sum([Q1 Project Budget]) + sum([Q2 Project Budget]) + sum([Q3 Project Budget]) + sum([Q4 Project Budget])  sum([Project Expenditure Amount]))
/
sum([Project Expenditure Amount])
Regards,

Re: Variance Calculation
Andrea Gigliotti Aug 8, 2017 5:26 AM (in response to Alison Cooke)i think you have a problem with brackets:
try this one:
( sum([Q1 Project Budget]) + sum([Q2 Project Budget]) + sum([Q3 Project Budget]) + sum([Q4 Project Budget])  sum([Project Expenditure Amount]) )
/ sum([Project Expenditure Amount])

Re: Variance Calculation
Alison Cooke Aug 8, 2017 5:36 AM (in response to Andrea Gigliotti )Hey Andrea,
Thank you for response.
I have tried that calculation and I am still getting the wrong result:
For example:
Actual Budget Variance
Back Office Administration Salaries (Integral) 399486 477936 9.76739...
The Actual and Budget are both correct.
But the variance is not. By your calculation it should be 0.196377...
I don't know what's going on. I feel that the data mustn't be loaded incorrectly or else the acutal and budget columns wouldn't be correct. But I also think there's no issue with the expression you've offered. What's going on?

Re: Variance Calculation
Alison Cooke Aug 8, 2017 5:39 AM (in response to Alison Cooke)I removed sum before the Project Budget and it works.
So the expression is this:
(([Q1 Project Budget]) + ([Q2 Project Budget]) + ([Q3 Project Budget]) + ([Q4 Project Budget])  sum([Project Expenditure Amount]) )
/ sum([Project Expenditure Amount])
It now works!
Why was sum causing issues?

Re: Variance Calculation
Andrea Gigliotti Aug 8, 2017 5:45 AM (in response to Alison Cooke)is not so easy to help you deeper without a sample app.

Re: Variance Calculation
Alison Cooke Aug 8, 2017 5:47 AM (in response to Andrea Gigliotti )Hi,
OK no worries. Thank you for your help regardless.




