8 Replies Latest reply on Jan 23, 2014 2:59 PM by andy.yu

    Variance Report Calculation Question


      Hello everyone,


      We are currently still on Tableau 7


      I'm currently trying to create a variance report, and I am having issues with the way the user would like it displayed.


      I don't think it's possible to do in Tableau, but I would like anyone's suggesstion if it is possible to do.


      Example of Report


      • It's a basic report, which compares "Actual" and different type of Budgets (e.g., Final Budget, Operations Budget, etc..)
        • Actual vs Final Budget
        • Actual vs Operations Budget


      So we repeat using "Actual" to compare our Final Budget and again with our Operations Budget.


      Is it possible to repeat the "Actual" column again? Or is there a way I can format it to display like the example of the report above?


      Any help or guidance would be greatly appreciated, thanks so much in advance.

        • 1. Re: Variance Report Calculation Question
          Aaron Clancy

          This looks easily doable assuming your data is structured properly.

          Do you have a workbook you could provide that has example data?

          • 2. Re: Variance Report Calculation Question
            Phillip Burger

            I would probably use a calculated field.


            There might be a more efficient way to do this for when the data set is very largeI look forward to reading other posters' comments on this question.

            • 3. Re: Variance Report Calculation Question

              Thank you everyone for the reply.


              - Aaron

              I unfortantely cannot provide a workbook since the data is somewhat sensitive. I know I can probably do it, but it would involve hardcoding things, which I would like to avoid.

              • 4. Re: Variance Report Calculation Question
                Aaron Clancy

                Pretty positive you don't have to hard code anything but that all depends on how your data is structured.   If you anonymized just a few lines of your underlying data, or just created a few fake records so people trying to help you with your question could see your data structure, it would probably get you an answer fairly quickly.

                • 5. Re: Re: Variance Report Calculation Question

                  Thanks Aaron.


                  Attached is an Excel file containing raw data that I made more generic. I was trying to play around with it last night, but had to do some hard coding to make it work, which I wanted to avoid.

                  • 6. Re: Variance Report Calculation Question
                    Natraj Kanamarlapudi

                    Agree with Phillip, I would use a calculated field to display the column again.  To keep it simple (Best practice) I would just show it once on the dashboard than duplicating same info.

                    • 7. Re: Variance Report Calculation Question
                      Phillip Burger

                      The attached workbook is an example solution. It uses calculated fields and Tableau's automatic aggregation. There might be better or an especially ninja way to do this, e.g., using table calculations or windows() functions. Given the shape of the data, the attached solution is a good start.


                      A problem you might have encountered and something that I think can stump many users is how to get the percentages in there without them aggregating as SUM().  What I did is place the percentage calculated fields one at a time to the Columns, let Tableau change the worksheet to a bar chart, then change the worksheet back to a table. This changes the percentage calculated fields from a SUM() aggregation to just AGG(). Automatic. Give it a shot. I don't understand it and hope another member of the community has a better idea and understanding on how to control theses percentage fields.


                      This image shows the worksheet from the workbook that I attached to this reply. The data used is the data that you provided.




                      A sidebar to your question is the shading. I hacked the shading using column banding. I would probably try to shade based on two panes Final and Operations. I'd would want to create a new dimension called something like pane_shading, show both values on the Filter shelf, and fill the respective panes with selected color. Given the shape of the data, I don't think it's possible to code the data as either Final and Operations. (Any other community members reading this far who have another idea on this?)


                      I look forward to reading other suggestions and ideas that the community might have. :-)

                      • 8. Re: Variance Report Calculation Question

                        Phillip thank you so much!


                        I was attempting to use table calculations / window functions to do it. Did get very far with it, I think I made it more complicated than it should be. The way you did it with calculated fields seems like it does the trick too!


                        No worries about the shading, that was partially my own doing, just wanted to highlight the different sections of the report. I almost had the same logic you had, but I was hardcoding the values instead of using the "CONTAINS" funtion like you did.


                        Thank you so much for everyone's help!