6 Replies Latest reply on Jul 17, 2018 6:05 PM by Grace Raj

    Actual vs Budget Comparision

    Kailash Negi

      Hi Everyone,

       

      I am a newbie to tableau and am sorry if the questions sounds like dumb. Actually I am trying compare Actual vs Budget in tableau, for this I have created a side by side bar graph which shows Actual and Budget side by side for each year however I also want to show the variance% on top of this bar graph as a line chart.

       

      I have attached the packaged workbook here and my tableau version is 10.0.5. Could you guys please help?

       

      I would also appreciate if someone can suggest a better way to visualize this.

       

      Thanks!!

        • 1. Re: Actual vs Budget Comparision
          Wesley Magee

          Kailash,

          You could try a layout like this:

           

          This is a dual axis chart. I created a variance calculation that looks like this:

          (SUM([Actual]) - SUM([Budget])) / SUM([Budget])

           

          I also created a calculation to color the bars whether they are above or below budget that looks like this:

          IF [Budget Variance] > 0 THEN "Exceeded Budget"

          ELSE "Below Budget"

          END

           

          Let me know if any of this doesn't make sense.

          -Wesley

          • 2. Re: Actual vs Budget Comparision
            Bryce Larsen

            Hello! Quite similar to the above, I colored each bar based on performance vs budget (outperforming, underperforming, and no budget are blue, orange, and gray, respectively). Budget is shown as a Gantt bar. I chose this over using a reference line due to the simple fact that you can't customize tooltips on the reference line.

             

            I bolded the performance (Actual) and then placed a colored arrow above each bar with the variance %. I looked into doing the variance trend line as you mentioned, but a lot of the time the variance was a negative number, so this made it a bit difficult to graph on top of the bars if doing dual axis.

             

            So, instead I did Actual + Budget as reference line and then Variance as a line graph stacked on top of one another to ensure they stayed separated. Then I removed some of the grid lines accordingly and added banding to group the Measures together:

             

            Maybe this is close to what you wanted? I woudl upload but unfortunately I'm using 10.3.

             

            Best,

            Bryce

            • 3. Re: Actual vs Budget Comparision
              Kailash Negi

              Thanks Wesley Magee

               

              However I want to show it in side by side bar graph and variance as you're showing currently.

              Also I want to have colors highlight the variance that is >5% or <5% instead of absolute above or below budget?

               

              Thanks a lot for the help!!

              • 4. Re: Actual vs Budget Comparision
                Kailash Negi

                Hi Bryce Larsen

                 

                The second one is close to what I need however I would want to show it as a side by side graph and then the variance as a line graph as you've correctly shown. Also can we have colors highlight the variance above and below a certain percentage?

                 

                It would be great if you can explain the steps since you can't post the .twbx because of version issue.

                 

                Thanks!!

                • 5. Re: Actual vs Budget Comparision
                  Bryce Larsen

                  Hello! Sorry for delay. Essentially you just make have two metrics:

                  • Actual, as already defined in the data
                  • Variance - a calculated field of: (SUM([Actual]) - SUM([Budget])) / SUM([Budget])

                   

                  You can then use the Variance result to create a field that will be used to color: CF_Variance_Color

                   

                  Here you can just make a discrete calculated field:

                  IF ISNULL([Budget]) THEN 0

                  ELSEIF [Variance] > .05 THEN 1

                  ELSE -1

                  END

                   

                  This will assign all metics/dates that don't have a budget a 0, all that are 5% ahead of budget a 1, and all that are below or up to 5% ahead of budget a -1. You can use these to color.

                   

                  I then put both of these on the Rows shelf along with the Metric and added some row banding to color and group the Metric calculations together.

                   

                  Hope this helps!

                   

                  Bryce

                  • 6. Re: Actual vs Budget Comparision
                    Grace Raj

                    Hi Bryce,

                     

                    Can you please share workbook package for this sheet..It will be help to follow the steps.

                     

                    Thanks

                    Grace