
1. Re: Variance Computation
Joshua Milligan Feb 27, 2013 10:11 AM (in response to smitha maddala)Smitha,
The answer will depend on exactly how your data is structured.
If Estimated Cost and Actual Cost are two separate measures, you might write a calculated field with code like this:
(SUM(Estimated)  SUM(Actual)) / ((SUM(Estimated) + SUM(Actual)) / 2)
If that doesn't work, please post a packaged workbook so I can see the specifics of your case. Thanks!
Regards,
Joshua

2. Re: Variance Computation
Joshua Milligan Feb 27, 2013 10:12 AM (in response to smitha maddala)By the way, Welcome to the Forums!

3. Re: Variance Computation
smitha maddala Feb 27, 2013 10:31 AM (in response to Joshua Milligan)Thanks Joshua. I tried using the calculation that you provided, unfortunately didnt quite work. Attached is the workbook if that helps at all. Thank you!

TOALv2.twb.zip 166.3 KB


4. Re: Variance Computation
Joshua Milligan Feb 27, 2013 10:40 AM (in response to smitha maddala)Smitha,
Thank you very much. Unfortunately, I'm not able to open the workbook because it doesn't contain the data. The one you posted is an unpackaged workbook (.twb file) and just contains a link to the Excel file.
Would you be able to save your workbook as a packaged workbook (.twbx file) with extracted data and repost?
Regards,
Joshua

5. Re: Variance Computation
smitha maddala Feb 27, 2013 1:26 PM (in response to Joshua Milligan)My Apologies. I believe I did right this time. Please let me know if it doesnt work. Thank you!

TOALv2.twbx.zip 184.1 KB


6. Re: Variance Computation
Joshua Milligan Feb 27, 2013 1:50 PM (in response to smitha maddala)Smitha,
See if this is what you are looking for:
SUM([Actual Costs])/SUM([Estimated Costs])  1
Tableau has three levels of calculations.
1. Row Level. This is what you had. ([Actual Costs] / [Estimated Costs])  1
What this does is perform the calculation for each record. So at the record level it is correct. But then if you add them all up it doesn't have any meaning.
2. Aggregate Level. SUM([Actual Costs])/SUM([Estimated Costs])  1
This is saying, "Add up all the Actual Costs (at whatever level of detail is in the view); Add up all the Estimated Costs (at whatever level of detail is in the view); and Then divide and subtract. This way, the calculation works at an aggregate level.
3. Table Calculations. These are very powerful, and I'd suggest reading the knowledge base articles on these after you are comfortable with Row and Aggregate level calcs.
I've reattached the file. Hope that helps!
Joshua

TOALv2.twbx.zip 174.8 KB
