VizWiz

Launch, grow, and unlock your career in data

March 29, 2013

Tableau Tip: Displaying multiple, disparate measures on multiple rows

16 comments

I received a great question from a colleague today.  Here’s the situation: You want to look at five measures.  You want three measures on the first line chart and two measures on the other line chart.  BUT, you want them all in the same view, i.e., one worksheet.

In the end, you want something like this:

image

This example uses the Superstore Sales data that comes with Tableau.  I’ve filtered the Order Date to 2012.

Step 1: Right-click drag the Order Date field onto the Columns shelf and choose a date aggregation.

image

Choosing the continuous Month, you get this:

image

Step 2: Drag your first measure onto the Rows shelf.  I used Sales.

image

Step 3: CTRL+Select the other measures you want on the same chart as Sales and drag them onto the Sales axis.  I chose Profit and Unit Price.  You should see multiple green rulers on the axis before your drop them.

image

The chart should look like this:

image

Now we want to add a seconds row that contains Profit Ratio and Discount.

Step 4: Drag Profit Ratio onto the Rows shelf.

image

Step 5: Drag Discount to the right side of the Profit Ratio chart.  You should see a dotted line before you drop it.

image

The result is a dual-axis chart on the second row.

image

Step 6: Right click on the Discount axis and choose Synchronize Axis

Step 7: Right click on the Discount axis and uncheck Show Header.  This hides the right axis.

Step 8: Clean up the axis titles and formats on the left and you’re done.

image

There are two catches to this type of chart.

  1. You can have as many measures as you want on the top chart.  Simply drag them onto that axis.
  2. You can only have two measures in the bottom chart, one of the left axis and one on the right axis.

If someone knows how to get around #2, I’d love to hear how.

Download the workbook here (Tableau 7).

16 comments :

  1. Challenge accepted, on the #2 point. My solution:

    http://goo.gl/Dptez

    Let me know if this works for you :)

    ReplyDelete
    Replies
    1. (For some reason my comment was removed, trying again):

      Here is an another:
      http://public.tableausoftware.com/views/examplescafoldunpivot/Result

      Delete
  2. hmm, weird, I can only get the effect of (3) to work as documented here if I use a dual axis by dropping the new measure over to the right, then synchronize - if I try your way I get a split side by side line chart

    ReplyDelete
  3. ..solved.. because I had a YEAR colour break already assigned

    ReplyDelete
  4. Hi, I have a combined charts visual (Lines on top of Bar chart), now, I want to have the Bars change their colors to Red from Green when the Lines are through. Say the measures for Lines are Profits while the Bars rep. the Turnover. If you have any advise, appreciate it.

    I will try to keep active this sheet to check for updates, however, I wish if anyone of you writes copies me on my email: murali.govindu@gmail.com

    Thanks and looking forward,

    ReplyDelete
    Replies
    1. What do you mean by "when the bars are through"?

      Delete
  5. say when the Lines intersect with the Bars, then the Bars should change their color, and when the Lines passes above the Bars, the colors of the bars shouldn't change. Something like this will do. Hope I could clarify my question.

    ReplyDelete
    Replies
    1. I wouldn't recommend this approach since you're not comparing the same metric. You might be misleading your viewers.

      If you insist though, you can so it thorough a simple calculated field that is one measure minus the other than place it on the color shelf for the bars.

      Delete
    2. Thanks for response. This is relevant visual since I want to know which Bars have short fall over a period of time against set targets. The minus calc. field will not give desired result. I can give my fresh thoughts over example:

      We have Period (date fields), Budgets and Actuals in this example, we can build simple Bar and Line chart combination visual. Now, we will see some Bars (Actuals) are short of the meeting Lines (Budgets) going over the Bars. Now, if the visual has bi-color i.e., with Budgets meeting Actuals (Green) and not meeting the Actuals (Red), we can notice easily the short fall.

      Let me know if you have any thoughts on the above,

      Thanks again,

      Delete
    3. If you have two distinct measures (Actual and Budget), then you should be able to create a calculated field that compares the two. Are you working with a cube or something? If you post a sample workbook, I can help you out.

      Delete
  6. Hi, Looks like responses on this blog appears in delay. The data is not from a Cube, it can be an excel too. I am not sure how to attach a workbook here and I see no option.

    The data below would help create a Bar (Actuals) and Line (Budget) chart in a visual with a dual option so that the Line chart go over the Bars:

    Fields Actuals Budgets
    siri 90 100
    raji 80 75
    snig 60 25
    megh 50 100
    vani 10 22
    madha 18 25
    uma 75 65
    latha 54 54
    niraj 75 87
    marin 87 75
    cyndi 75 82
    other 75 75

    Here I would like to see if the Bars in Red if below the Line and Green if they are above Line. I used sum and windows sum/ avg but couldn't achieve this desired results.

    Looking forward to hearing from you, Regards, murali.govindu@gmail.com

    ReplyDelete
    Replies
    1. See if this workbook does the trick for you. https://dl.dropboxusercontent.com/u/14050515/VizWiz/Bar%20%28Actuals%29%20and%20Line%20%28Budget%29.twbx

      Note that this was built with Tableau 8.2 so you will need to have that version. If you have 8.1, let me know.

      Delete
  7. Hi Andy,

    I have a similar request but instead of measures, how can we do this with dimensions.
    How can you show the breakdown of a measure by different demographic dimensions, without drilldown.
    It would great if you could point me to an example.

    G

    ReplyDelete
    Replies
    1. Gautam, just put the dimension you want to split up your measure by on the color shelf.

      Delete