11 Replies Latest reply on May 18, 2017 9:33 AM by Edward Carrillo

    Combination Chart w/ 3 Chart Types

    Edward Carrillo

      Hello,

       

      Is there a way to create a combination chart with 3 different chart types (area, bar, line)? In the attached workbook, I used the dual axis feature to combine the area and bar chart into one, but have the line chart separately below that. I want to display all of this together, as is, in one chart? Thank you in advance for the help.

       

      Respectfully,

      Eddie

        • 1. Re: Combination Chart w/ 3 Chart Types
          Daniel Vincent

          You can per se but two things...why do you need so many different charts on the same axis/area and are you okay with having a pseudo area chart?  I would rethink the need and provide a clearer picture vs forcing square peg into a round hole.

           

          Now youcan accomplish something like this using reference lines/shading below but it is not going to have those smoothing/rounding edges of a true area chart.  Screen Shot 2017-05-15 at 2.06.20 PM.png

          • 2. Re: Combination Chart w/ 3 Chart Types
            Edward Carrillo

            Hi Daniel,

             

            All four visuals display different metrics and I need them on the same chart. The area chart shows the budget, the bars show what was actually spent, the line through now shows what was "earned" (I'm not sure if you are familiar with Earned Value Management), and the line that continues is my forecast.

             

            This chart contains cumulative data. I make this same chart for incremental performance as well, which makes the area chart much more useful than seems here. For that reason, the pseudo area chart wouldn't work here. It looks like this:

             

            As you can see, the BCWS area chart becomes more visually useful when it's shown incrementally rather than cumulatively. I just picked the cumulative data for my original question. Please let me know if you can think of another way to achieve this rather than with the pseudo chart. Thank you!

             

            Respectfully,

            Eddie

            • 3. Re: Combination Chart w/ 3 Chart Types
              GAURAV DHINGRA

              Hi Edward, Can you please share the sample workbook.

              Thanks

              • 4. Re: Combination Chart w/ 3 Chart Types
                Kaz Shakir

                Eddie,

                I don't really know or use EVM, but just based on what I have read about it, don't you always want to view these values in cummulative terms?  The incremental values don't really seem to have much meaning.  For example, the Earned Value for a project helps you to determine if you are under budget or over budget based on how much of the project has been completed, and the amount of money that has actually been spent.  So, you would compare the cummulative budget values to the cummulative EV values to determine if you are under or over budget, right?  And, if that's the case, simple line charts of the cummulative values for each of those values would provide a more useful chart.

                 

                I may not really understand your needs here.

                Kaz.

                • 5. Re: Combination Chart w/ 3 Chart Types
                  Edward Carrillo

                  Hi Kaz,

                   

                  I use both. The reason incremental values are important to me is so that I can see exactly what happened that particular month. You are right in that I am looking cumulatively at my budget over time for the duration of the project, but there are multiple metrics I'm comparing. Cost variance (BCWP - ACWP) and schedule variance (BCWP - BCWS). This is why all 3 of these metrics need to displayed over each other on one chart. And I have ETC (estimate to completion) as a forecast. I updated my workbook to have another sheet (first is cumulative, second is incremental). On the second sheet, I hid some values so that the picture was a bit smaller, but the data is all there still. Gurav kansal Workbook attached.

                   

                  The charts are built the same way, so if we can get one to have all 4 metrics overlap, then we can get the other to do so as well.

                   

                  Thank you for the help; I really appreciate it! And please let me know if you have any questions.

                   

                  Eddie

                  • 6. Re: Combination Chart w/ 3 Chart Types
                    Kaz Shakir

                    Eddie,

                    After doing some more research on the Earned Value Management methodology, I still think that you would be better off keeping much of the data separate rather than trying to fit it all on one chart.

                     

                    I've taken a shot at an alternate approach, and I'm attaching the workbook - please take a look and let me know what you think.

                     

                    Just as a snap shot, here are the four charts I came up with:

                     

                     

                     

                    Hope that helps.

                    Kaz.

                    1 of 1 people found this helpful
                    • 7. Re: Combination Chart w/ 3 Chart Types
                      Edward Carrillo

                      Kaz,

                       

                      I really like what you did with the shapes chart beneath the line graphs; and I think you make a good point - showing cost and schedule variances on the same chart can be a bit much visually. It looks great how you broke it up, and I would have never thought of using the Xes and checks to show the indices. Also, you calculating TCPI with Tableau gave me a lot of insight on the kinds of calculated fields I can make.

                       

                      Thank you so much for your help and ideas! I have one last question. I'm very on board with your Cost and Schedule chart methods now, but I feel like the one thing that isn't there is the easy visual that shows exactly what happened this month. I want to see performance cumulatively, of course, but when presenting metrics each month, I like to show what just happened in the current month (which is why I had the two tabs originally). So, current month CPI, SPI and Earned Value & Actual Costs. Would you recommend duplicating these same charts with the monthly data I have, or do you have any other suggestions?

                       

                      Thanks again! I really appreciate it.

                       

                      -Eddie

                      • 8. Re: Combination Chart w/ 3 Chart Types
                        Kaz Shakir

                        Eddie,

                        I'm glad that you liked the approach and found it helpful.  It was a fun problem to solve - and I learned a little something about a new technique (EVM).

                         

                        As far as showing what happened this month, you face one primary problem:  If you just compare the incremental values, for example BCWS for the month vs. BCWP for the month, is that the values for each of those variables for this month are not "fixed."  What I mean by that is that the value for this month for BCWP is actually based on how much variance there has been between BCWS and BCWP up to this point.  And since BCWP is calculated once, at the beginning, and then never changed after the budget is set, you are comparing something that's fixed (BCWS) with something that's dynamic (BCWP) - and the problem becomes that if there has been any variance between BCWS and BCWP up to this month, then you are comparing "apples and oranges."  For example, consider March 2016 - BCWP in that month is $171,515, and ACWP in that month is $221,181 - does that mean we over spent that month?  No.  Because if you consider the cumulatve numbers, you realize that based on the work completed to-date, based on the original budget, we should have spent $4,282,555, but we actually only spent $4,197,012 - so we are actually under budget.  Looking at the incremental values would have driven you to the wrong conclusion.

                         

                        All that said, there is actually a great solution.  The SPI and CPI actually perform the incremental analysis for you.  In your example, the CPI in March 2017 is 0.9171.  What this value tells us is that based on how much of the project we have completed by the end of March 2017, based on the original budget, we should have only spent $6,191,939; but instead we have actually spent $6,751,756, indicating we are about $560,000 over budget, or about 9% over budget (the 9% is 1 - 1/CPI).  And the same math applies to the SPI index as well.

                         

                        So, you see, the indices that you calculate as part of the EVM methodology are actually intended to give you the month by month performance indicators that you are looking for.

                         

                        Keep in mind that I am not an expert (and barely even a novice at this point) on EVM, but this is my interpretation of what I have learned to date.

                         

                        Hope that's helpful.

                        Kaz.

                        • 9. Re: Combination Chart w/ 3 Chart Types
                          Edward Carrillo

                          Hi Kaz,

                           

                          It isn't comparing apples and oranges because while the budget (BCWS) is set, the earned value and actual costs can vary. So you compare those, which change, to the budget, which doesn't. These variances are important because they allow you to determine whether you are under- or overrunning and ahead or behind schedule. You're right in that the cumulative metrics are the ones that matter overall, but in your example - did we over spend that month? - the answer would still be yes. As in that month, we spent more than we earned, and that is now a negatively contributing to our cumulative cost variance. This is why I like seeing monthly performance as well as cumulative, and why the PM's do as well.

                           

                          Also I just wanted to clarify for future reference - is there no way to make a 3-type combination chart in Tableau? There are other ways to display data, but to recreate that chart I made in Excel, is that impossible with Tableau?

                           

                          Thanks again for all the help!

                           

                          Best,

                          Eddie

                          • 10. Re: Combination Chart w/ 3 Chart Types
                            Kaz Shakir

                            Eddie,

                            Thanks for clarification.  You know more about the project management process than I do, frankly, so I'll defer to your knowledge.

                             

                            As far as the chart with three different types (bar, line, and area), or, for that matter, with more than 2 different types - I have not been able to figure anything out that would result in that chart.

                             

                            Kaz.

                            1 of 1 people found this helpful
                            • 11. Re: Combination Chart w/ 3 Chart Types
                              Edward Carrillo

                              Kaz,

                               

                              Great to know. I really appreciate your time and help with this and I will do my best to implement what you've taught me. Thanks!

                               

                              -Eddie