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.



        • 1. Re: Actual vs Budget Comparision
          Wesley Magee


          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"



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


          • 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.




            • 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.



                • 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



                  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!



                  3 of 3 people found this helpful
                  • 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.




                    1 of 1 people found this helpful