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.
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):
To understand the decision points, here’s the 2nd page of the PDF:
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!
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.
Hi Matt, Sorry about that, I fixed the PDF link now. Here it is, just in case http://drawingwithnumbers.artisart.org/wp-content/uploads/files/bars-and-lines-on-same-chart.pdf. Let me know if this helpful to you!
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?
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.
Jonathan, thanks for a quick reply. I’ve posted my query here
I hope this is what you meant by ‘link to it here’, I haven’t used the forum much so sorry if I didn’t get it right.
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?
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.
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
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!
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?
Can you (or someone else) please explain for me how I can download Worksheet1 – Worksheet9?
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.
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.
Try it now. Tableau Public recently changed how they deliver visualizations through iframes so this was temporarily broken. http://www.tableausoftware.com/public/blog/2014/03/fix-your-iframe-2386, it should be ok now. Thanks for letting me know!
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.
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.
Awesome post! Do you have any step-by-step instruction showing how to create worksheet 6, which is grouped bar with line?
Worksheet 6 should have a link to the KB article used to create it in the caption, just in case it doesn’t here’s the link: http://kb.tableausoftware.com/articles/knowledgebase/grouped-bar-chart
Pingback: Visualization - Tableau Tips,Tricks,Best Practices - Jenny (Xiao) Zhang
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
Hi, The download link is at the bottom of the Tableau view, just in case you’re not able to see that for some reason here’s a link to the workbook: http://public.tableau.com/views/barsandlines/Introduction?:embed=y&:display_count=no.
It’s a really great article! Helped me a lot today and saved me weeks of work! Thank you so much!
Hi Nikolay, I’m glad it was so helpful for you!
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.
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:
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.
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.
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.
Got It .Thanks Jonathan
I tried to follow but I can’t reproduce your chart #3Stacked Bars MV, Lines Discrete.
My problem is posted here: https://community.tableau.com/thread/203286
Please, I need a hand here.
Appreciate your help.
Hi Fabio, I’ve responded at the Community thread.
Thanks after a lot of manipulation, I was able to get it to work with my data.
Pingback: Cross Data Source Joins + Join on Calcs = UNION Almost Anything in Tableau v10.2 | Drawing with Numbers
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.
I just get dot instead of lines
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.
OK thank you Jonathan, I sent to your e-mail the workbook ok.
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.
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.
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.
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!
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.
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.
Hello Uzair, 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.
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.
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.