12 Replies Latest reply on Dec 12, 2019 6:30 AM by Nigel Applewhite

    Budget vs Actual and Variance

    Ozalee Francis

      Dear Tableau Community,

       

      I tried to reach the visualization in the attached picture, but I'm stuck with the variance functionalities in Tableau, which I completely understand in Excel.

      Actual vs Forecast.PNG

       

      I have prepared a data set including 3 sheets :

      - Budgeted expenses

      - Actual expenses

      - Expense variances

       

      I did an "inner join" first, but I changed for a full outer join but none of them worked. To be honest, I am a bit lost and I would like to get some highlights to build a Dashboard as presented in the picture. Maybe my data set is not a good example. If someone can tell if I'm going to the good or wrong way.

       

      My second goal would be to re-evaluate a budget forecast for the next 6 months based on the actual expenses.

       

      However, just resolving my first issue would tremendously help me to learn more about Tableau.

       

      Thanks in advance for your help!

       

      Francis

        • 1. Re: Budget vs Actual and Variance
          Graham Rose

          A few things that might help...

           

          First, your dataset. Right off the bat, I tend to organize my data in an un-pivoted table. Rather than having columns for each category (in your case 4 columns: Employee, Office, Marketing, and Training). Instead, you can drop that to two columns: the value and the category. You can then join on the months your budget and actual tables. So you would end up with something like this for your two month of data:

           

          From there, you don't even need the variance table. Instead, you can just make a new calculated field that would be SUM(Budget) - SUM(Actual).

           

          You could also reformat it to be even fewer columns:

           

          Here you can still use the same formula for the variance. Then you could graph the value and put Type to color to split the two lines.

          1 of 1 people found this helpful
          • 2. Re: Budget vs Actual and Variance
            Ozalee Francis

            Thank you Rose for your quick reply and advice! I will try it right away and let you know how it worked

            • 3. Re: Budget vs Actual and Variance
              Zhouyi Zhang

              Hi, Ozalee

               

              Here is my solution by union your data as shown below

               

              2nd, it is an optional in your case, you can either pivot it or keep it depending on your own demand.

              If you are not pivoting your data, when you need sum your measure, you probably need create a calculation for total cost like [employ cost]+[office cost]+[marketing cost]+ [training]

              and build viz like

               

              A sample workbook attached in your original thread for your reference. hope this helps

               

              ZZ

              • 4. Re: Budget vs Actual and Variance
                Ozalee Francis

                Hi Zhouyi,

                 

                Thank you for your suggestion, I think it is more accurate for the type of data I want to explore.

                 

                 

                I have a second question if I may ask. On the worksheet 4, I added a table in which I want to to add variance in percentage. Is it interesting to do that in a table? I will try a graph with a trend line of the variance. What do you think of it?

                 

                And to finish I tried to do the same graph as yours in the file "test" but it didn't work apparently, something is missing.

                 

                Thank you in advance

                • 5. Re: Budget vs Actual and Variance
                  Zhouyi Zhang

                  Hi, Ozalee

                   

                  Glad the solution works for you. I am sure where is your worksheet 4 and what's your expectation of bringing the variance to the viz? and your test file seems missing the data as package in your workbook.

                   

                  ZZ

                  • 6. Re: Budget vs Actual and Variance
                    Ozalee Francis

                    Hi Zhouyi,

                     

                    Yes sorry, the test file was missing. In this viz I tried to do the same viz as you did in the "Book 6" but doesn't look like yours. I can't incorporate the different bars.

                     

                    In the file "Book 6 v11b", "second worksheet" I want to add a row with the variance in percentage, but I'm still trying to figure out how to do that with a pivot value

                     

                    Many thanks for your help!

                     

                    FO

                    • 7. Re: Budget vs Actual and Variance
                      Zhouyi Zhang

                      Hi, Ozalee

                       

                      Is it something like this?

                       

                      Sample workbook attached.

                       

                      ZZ

                      1 of 1 people found this helpful
                      • 8. Re: Budget vs Actual and Variance
                        Ozalee Francis

                        Hi Zhouyi,

                         

                        Thank you so much! This exactly what I meant to.

                         

                        If I may, I have two very last questions:

                         

                        - What would be the formula of the variance between "Budgeted expenses" - "actual expenses" in the pivot table?

                         

                        - In the case I don't pivot my measures "employ cost /office cost/ marketing cost/ training", what would be the formula to calculate distinctly the total cost of "Actual expenses" and "budgeted expenses"?

                         

                         

                        Many thanks again

                        FO

                        • 9. Re: Budget vs Actual and Variance
                          Zhouyi Zhang

                          Hi, Ozalee

                           

                          Sorry about the late response.

                           

                          to your 1st question, you just need change the "Expense Variance" below to "Actual Expense" and in the end of the division, add "-1"

                           

                           

                          As your data structure, I don't think there is a way without pivoting, unfortunately

                           

                          ZZ

                          1 of 1 people found this helpful
                          • 10. Re: Budget vs Actual and Variance
                            Ozalee Francis

                            Hi Zhouyi,

                             

                            All is good, thank you so much for your precious help.

                             

                            I've learned handy tips about Tableau from you!

                             

                            Have a nice day

                             

                            FO

                            • 11. Re: Budget vs Actual and Variance
                              Zhouyi Zhang

                              No worries, I am glad to help Hope you enjoy with tableau

                               

                              ZZ

                              • 12. Re: Budget vs Actual and Variance
                                Nigel Applewhite

                                I have pre calculated Variance for six different variables across six Tableau worksheets. However when moving them across to the Dashboard only the first and last are displaying.

                                Does anyone have any idea as to how to get the variances to display dynamically (i have six different views of the data to show on the Dashboard)

                                 

                                I have already created a parameter and calculated field called "Select a Sheet"! but its not displaying the variance legend (though i can see the variance within the charts - they are working fine)

                                 

                                Any advice?