Stacked Bars and Lines

Bars and Lines

48 Replies

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!

48 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 ↓
  3. 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 ↓
  4. 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 ↓
  5. 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 ↓
  6. 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 ↓
  9. 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 ↓
  10. 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 ↓

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

  12. 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 ↓
  13. 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 ↓
  14. 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 ↓
  15. 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 ↓

  18. Pingback: Cross Data Source Joins + Join on Calcs = UNION Almost Anything in Tableau v10.2 | Drawing with Numbers

  19. Juracy Oliveira

    Hi Jonathan, I try to follow your Instructions for 5. Grouped Bars, Lines Discrete, but, not able to draw the lines , can you point to me to the right direction?

    Thank you in advanced.

    Reply ↓
    1. Jonathan Drummey Post author

      If you’re getting points instead of lines the first thing to check is your mark type, you’ll need to explicitly set it to Line. After that there are a number of reasons why you might be getting points instead of lines. In this particular view you’ll need to make sure that your equivalent of the Region position is returning a single date for each month that you want to position it on. Beyond that I’d need to see your view. You can email me at jonathan (dot) drummey (at) gmail and I can take look, if you can’t share a packaged workbook with sample data then I’d need screenshots of the workspace with all Shelves and the formulas of the calculated fields.

      Jonathan

      Reply ↓
  22. Nikhil Sahni

    Hi Jonathan,
    It is definitely helpful for me. However I don’t understand how did you have Mark Type dimension in the your data. It doesn’t seem like a calculated field. Please help me understand. Really appreciate it.

    Reply ↓
    1. Jonathan Drummey Post author

      Hi Nikhil, I’d made Mark Type a field in the underlying source which is a “tall” source with one record per measure/order date combination. You could do the same with calculated fields or ad hoc groups.

      Reply ↓
  23. Flavia Alvarez

    Hi Jonathan, i want to create a graph like #5, I’d like to put on graph sales and inventory broken down by year, so I need grouped bars por the inventory and diferent lines for the sales. The axis is in weeks (discrete).
    Can you give me somo guide? is there a chance yo have the sheets in this article?

    Thank you in advanced.
    Flavia

    Reply ↓
  24. Alice Haslett

    Hi Jonathan,

    Thanks so much for this!

    I was wondering if you could please help me with something. I am trying to make a graph with Actual 2018 Sales, Budget 2018 Sales, and Actual 2017 Sales. My boss would like the Actual 2017 Sales to be a line, with the other two being stacked bars based on location.

    I have been able to make it so that:
    – All 3 are shown as stacked charts
    – 2017 figures are a line but the bar charts for budget and actual 2018 are stacked on top of each other
    – 2017 figures as a line, but the bar charts for budget and actual are overlapping, with budget 2018 figures shown as a thinner line inside actual (which is a problem as it does not allow the breakdown by country)

    Could you please possibly advise on how I could have actual and budget side by side with a line at the top showing the actual of 2017?

    Thank you in advance!
    Alice

    Reply ↓
    1. Jonathan Drummey Post author

      Hello Alice, to it sounds like you want grouped stacked bars (for the 2018 sales & budget) with a line…without knowing your data structure I can’t give you an answer exactly (note the questions in the flowchart about whether the values are coming from measures or whether there is a dimension. I’d need to see a workbook to understand what you are trying to do. Please post a thread on the Tableau forums http://community.tableau.com with a Tableau packaged workbook and some sample data and your work so far and feel free to link back to that post here and I’ll take a look when I can.

      Reply ↓
  25. Uzair Rasheed

    Jonathan,

    I want to create a graph like 8b but one of my line graph would use a calculated field to get populated. I used to dual axis approach but it is allowing me to only show one line graph. How can I upload 2nd line graph using the calculated field.

    Reply ↓
  26. arpitsinha

    Hello Jonathan,

    I am not getting your flow chart approach. I just am migrating few excel charts into Tableau Charts.

    I have as many as 7 measures from which I have to put 5 into bar and 3 in to line.

    Reply ↓
    1. Jonathan Drummey Post author

      Hi, sorry for the late reply. Personally I’d strongly recommend revisiting the purposes of those charts and the possibility of creating different kinds of views that could answer the business questions rather than pushing Tableau to generate a view like that. In order to generate the combo chart you describe you’re going to have to pivot at least the 5 measures, if not all 7 or 8 measures. If you’re still looking for help with this then please post a thread on the Tableau forums http://community.tableau.com with a Tableau packaged workbook and some sample data and your work so far and feel free to link back to that post here and I’ll take a look when I can.

      Reply ↓

Please add your thoughts and perspectives