Stacked Bars and Lines

Bars and Lines

This is a post about getting the output you want, despite what Tableau thinks.
This is a post about making Tableau do what Excel can do (whether it’s a good idea or not).
This is a post about gaining better understanding of dimensions, measures, continuous, and discrete.
This is a post about putting bars and lines on the same chart.

6. Grouped Bars Lines MV

Getting bars and lines on the same chart can be as easy as dragging and dropping a few pills, or as difficult as a lot of custom calculations and SQL hacking. What in Excel might take three clicks – select the series, click on the Chart Type button on the Ribbon Bar, choose the specific chart type – in Tableau could take a few clicks, or a whole lot of typing.

This is due to very different design philosophies between the two applications, the key difference being that Tableau generates the view from attributes of the data, which gives Tableau tremendous power, whereas in Excel you can just select cells A2, B2, and C48926 and tell Excel to draw a grouped bar chart. In Tableau, is the distinction between those three values based on a Dimension like Region, or are you looking to plot three different Measures like Sales, Profit, and Budgeted Sales? That distinction matters when you are looking to put bars and lines in the same view, so here’s a quick overview.

In Tableau, there are Dimensions – columns of values within the data that are used to partition the view; and Measures – columns of values whose exact displayed value in the view depends upon the chosen set of Dimensions. Both Dimensions and Measures can be Discrete – blue pills, having categorical values that generate headers in the view; or Continuous – green pills, having a continuous range of values that generate axes in the view. To help understand this better, check out these posts by Tom Brown: http://www.theinformationlab.co.uk/2011/09/23/blue-things-and-green-things/ and Joe Mako: http://www.thedatastudio.co.uk/blog/the-data-studio-blog/groundwork-for-custom-table-calculations.

Once you have an understanding of how your data is represented in Tableau, and have an idea of the combined chart you want to build, then you can use the handy The “So You Want Bars and Lines on the Same Tableau Chart?” Flowchart (PDF):

bars and lines on same chart - flowchart

To understand the decision points, here’s the 2nd page of the PDF:

bars and lines on same chart - instructions

Each of the worksheets described is in the following Tableau Public workbook, along with instructions on how to set up the worksheet and prepare the data.

The genesis of this post has been the series of questions on the Tableau forums about building combined bar and line charts, and hopefully this resource will help take care of future questions. So another person to thank for this is Larry Wall, who came up the the three virtues.

If you’ve found other (easier?) ways to do this, or have questions, please let me know!

33 thoughts on “Bars and Lines

  1. Matt Lee

    Hi Jonathan,
    This is a great article! Is the PDF available? I can’t seem to get to it when I click on the link.
    Would love to be able to print this out and to keep it as a reference when I use Tableau.

    Reply
    1. Anuja

      Hi, I have a Bar chart with a line graph as a dual axis. But in this I have added a condition to my “number of records” which says, show records of “at least 10”. So, all cases where the number of records is below 10 will disappear. However, the line graph (or the point marker) for such bars do not disappear. How can I make a condition on a filter apply to both axes?

      Reply
      1. Jonathan Drummey Post author

        Hi Anuja,

        You didn’t specify anything about what is making the bars and lines, i.e. what your data is like, are you using dimensions or measures, what the level of detail is for each Marks Card. For me (or anyone else) to help, a packaged workbook or Tableau Public viz with some sample data that represents your situation would be best. If you post on the Tableau Forums, link to it here and I’ll take a look.

        Reply
  2. Jason

    This is helpful – so much of Tableua (and excel for that matter) is how the data is structured. I’m still struggling with a chart I’d like to build. I want to have months as the x-axis and then two y-axes – the first is the annual month total which is a bar. the second is the cumulative YTD total as a line. To top it off, I am showing years as different lines (or bars). (so 2013, 2012, and 2011 are each their own line). I think the issue is the aggregated sum is continuous, but I need to show discrete months so I can show different years. Any suggestions?

    Reply
    1. Jonathan Drummey Post author

      Hi Jason,

      I think what you want can be done, but seeing your data would be a big help. You can either post something on the Tableau forums and ping me or send me an email and I’d be glad to take a look.

      Jonathan

      Reply
  3. Jason

    Thanks! I just sent an email as I can only make the view in Excel so i sent a simplified version. Even knowing if I can’t do it – or how to restructure data – will be very helpful

    Reply
  4. stacey

    In looking at the workbook on tab 1, I am wondering how you got the date to be a green/continuous field – it can be drilled down on, but when I added my date to my workbook and it was blue (it is a dimension like yours) I changed it to continuous to make it green and the drill down disappeared. There was no discussion of the date field/dimension in your instructions, so I just want to make sure I have it right as it looks like it is not continuous except for the color on your workbook.. Reply via email AOK! Your workbooks and posts have been such a help! Thank you!

    Reply
    1. Jonathan Drummey Post author

      I’m not sure what you meant by “the drill down disappeared,” there’s always a drilldown for dates until we get to the smallest built-in aggregation. A messy part about continuous dates is that there’s no drill up capability.

      As far as how I got that date, I usually bring dates in to the view by a Right click+drag operation, when I let go of the pill on the appropriate Shelf Tableau displays a dialog offering different date aggregations, in this case I chose the green datevalue/datetrunc version of MONTH(Order Date). Does that help?

      Reply
  5. Brian Frederiksen

    Hi Jonathan
    Can you (or someone else) please explain for me how I can download Worksheet1 – Worksheet9?

    Kind Regards
    Brian

    Reply
    1. Mohan Jiang

      Hi Johnathan,

      This is really great and helpful post. But can you tell me where i can download the excel workbooks and Tableau examples? I seem cannot find the link.

      Thank you.
      Mohan

      Reply
      1. Jonathan Drummey Post author

        There’s a “Download” link just above the Tableau logo at the bottom of the viz, you can use that to download the .twbx file. It uses the Superstore sample data that ships with Tableau.

        Reply
  6. fooangel

    We have stacked bars showing a total # on top, comparing monthly year1 over year2 along x axis; volume on y-axis; individual numbers on hover for each of the stacks;
    Business wants a line graph along the top of the year2 bars, connecting the total.
    We use tableau.
    Any input?

    Reply
    1. Jonathan Drummey Post author

      Hi, I’m unable to help because there are too many variables that you haven’t specified, such as the structure of your data. That’s what the flowchart above is intended to help you walk through. If you follow that and can’t get your desired results, then please either post a viz with some sample data and your work so far to Tableau Public and link to it here, or post a packaged workbook on the Tableau forums and link to it, then I or someone else can take a look.

      Reply
  7. Vincent

    Hi Jonathan,

    Awesome post! Do you have any step-by-step instruction showing how to create worksheet 6, which is grouped bar with line?

    Thanks,

    Vincent

    Reply
  8. Pingback: Visualization - Tableau Tips,Tricks,Best Practices - Jenny (Xiao) Zhang

  9. Malena

    Hi , where is the Tableua public workbook you are talking about? I want to replicate sheet 8a based on the algorithm above, but there is no sheet 8a. Thanks

    Reply
  10. Nikolay

    Hi Jonathan!

    It’s a really great article! Helped me a lot today and saved me weeks of work! Thank you so much!

    KR
    Nikolay

    Reply
  11. Yashwanth

    Hi Jonathan,

    I have data break by weeks, which is discrete.Can I generate the grouped bar chart on one axes while keeping the weeks column discrete.I am trying to show sales across weeks by bars on the dimension (TY LY)one bar for this year and other for last year in the same axes.
    Please can you advise.

    Reply
    1. Jonathan Drummey Post author

      Hi Yashwanth,

      Assuming that TY and LY are two different measures then I’d set this up using the Week dimension on Columns and then a Measure Names pill to the right of that, with Measure Values on Rows, something like this:

      grouped bars

      If this isn’t what you’re looking for, then I suggest you post a Tableau packaged workbook with some sample data and a mock-up of your goals to the Tableau forums at http://community.tableau.com, link back to it here and I’ll take a look.

      Jonathan

      Reply
  12. Yashwanth

    Hi Jonathan,

    Thanks for the reply.
    I am trying to generate the visualization similar to the worksheet(Grouped Bars and Line MV) in the Bar and Line Workbook.But I have data at week level and week is the discrete field.Can you please advise in this case.

    Reply
    1. Jonathan Drummey Post author

      You’re not going to be able to do this using a discrete week pill, you have to use a continuous date pill at the day level of detail where a custom calculation (Region Position in the Bars and Lines workbook) is used to offset the days appropriately in order to draw all the bars. Also, to do this per the #6 Grouped Bars and Line MV the data source is duplicated to have sufficient dimensionality in order to generate that calculation. Please make sure to follow the instructions in the Captions of each sheet, you’ll need to look at sheet #5 and sheet #6.

      Jonathan

      Reply

Please add your thoughts and perspectives