VizWiz

Launch, grow, and unlock your career in data

February 4, 2015

Tableau Tip Tuesday: Display the Total on Top of Stacked Bars (without Using the Secondary Axis)

10 comments
I've written previously about adding totals on the top of stacked bars. In that example, I used a secondary axis to add the totals. However, what if you're already using the secondary axis? As an example, consider this view:


To display the total for each year on top of the stacked bars, follow these steps.

Step 1: Change the mark type for the left axis to Gantt.


Step 2: Right-click on the Sales pill and add a Running Total Quick Table Calculation
Step 3: Change the Compute Using to Department. The view should now look like this:


Step 4: Create a calculated field that returns the negative Sales.


Step 5: Add this new calculated field to the Size shelf for the Sales axis.


The viz should now look like a stacked bar chart.


Step 6: Create a calculated field to return the running total of sales, but only return it for the top bar.


Step 7: Drag this new calculated field onto the Label shelf for the Sales axis.
Step 8: Change the Compute Using to Department.

That's it!  The view will now look like a stacked bar chart with the total of each year on the top.


Download the Tableau workbook here.

10 comments :

  1. Why not just add a reference line that Sums each Cell, and format the line style to None? Tableau Calculations add needless complexity in this situation.

    ReplyDelete
    Replies
    1. I totally over complicated this solution Joe. You're absolutely right. It was fun figuring this out though. So at least I learned something too. 😊

      Delete
    2. The technique you used is useful in other situations, see for example a divergent bar chart with one segment centered over the zero line that uses a Gantt bar: http://www.datarevelations.com/using-tableau-to-visualize-survey-data-part-2-%C2%BD.html

      Delete
  2. I'd say LOD calcs add another way to deal with this.

    ReplyDelete
  3. I have a problem that relates to this post and some of the comments.

    I want to display the top 5 and get the total for those top 5. So if I used the index field and showed top 5 by category, and then subtotaled on the category I'd see the right answer. But the subtotal makes a needless line and I can't hide the non-subtotaled lines. Total can't use Table Cacls.

    Joe Mako's suggestion of using the reference line worked like a charm and gave me what I wanted for one chart. It gave me the total for the top 5. However I needed a tweak visually and does not work in that situation. What I really want is [Category] : [Top 5 subcategory Total (discrete)] : Bars (each of the top 5 sub-cat is a box in the total) : Some top 5% of total (optional)

    First of all I remembered window_sum has start & end, so I used that in conjunction with lookup() to display the total for the top 5 subcategories on a Max(1) field. Suggestions outlined in the blog and the comments helped direct me to the solution.

    ReplyDelete
    Replies
    1. I'm happy to hear the community (and this blog) has worked well for you!

      Delete
    2. Community is essential. I recall reading a book where the author referenced someone who tried to live a solitary stone age life and concluded that a single person could not ever do it.

      Delete
  4. This was really helpful Andy - I tried initially adding a reference line but not getting any results prob due to dual axis and having another reference line on the other axis (I was only getting the total in top left). This workaround solves my problem. Thanks!

    ReplyDelete
  5. Hi Andy, this is a great solution! however do you know if there's a way to display the total at the base of the bar graph? I some sub-totals already and dual axis so I'm trying to make sure labels don't interfere with each other. Thank you!

    ReplyDelete
    Replies
    1. You could create a calculation that returns the overall value for the stack and then add another bar at zero and label that.

      Delete