6 Replies Latest reply on Feb 27, 2013 1:50 PM by Joshua Milligan

    Variance Computation

    smitha maddala

      Hi! I am a newbie, trying to play around in Tableau. I am looking to compute a Variance percentage between the columns - Estimated and Actual Costs. What is the easiest way to create this calculated field? Any help would be much appreciated. Thanks!

        • 1. Re: Variance Computation
          Joshua Milligan

          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

            By the way, Welcome to the Forums! 

            • 3. Re: Variance Computation
              smitha maddala

              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!

              • 4. Re: Variance Computation
                Joshua Milligan

                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

                  My Apologies. I believe I did right this time. Please let me know if it doesnt work. Thank you!

                  • 6. Re: Variance Computation
                    Joshua Milligan

                    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 re-attached the file.  Hope that helps!

                    Joshua