Friday, February 1, 2013

Formatting Individual Columns in Tableau (Dual-Axis Crosstabs)

Today, we will look at a special type of graph in Tableau, the "Dual-Axis Crosstab."  The inspiration for this method comes from Conditional Formatting, which I know was at least partly developed by Jonathan Drummey.

In it's basic form, Tableau can only format based off one value.  For example, you can have a chart that displays Sales and Profit, but you can only color based on one of them.
Profit and Sales by Region (Colored by Profit)
Now we have an individual color for each row, when we really want one for each cell.  We can work around this by dragging "Measure Values" to the color shelf.  While this gives us an individual color for each cell, it puts them all on one scale, which is foolish in this case.
Profit and Sales by Region (Colored by Measure Values)
What we really need is to be able to apply an individual formatting to each column.  This is where the "Dual-Axis Crosstab" comes in.

Step 1: 
  • Create a meaningless calculated field
Calculated Field
This field will be used as the base for our columns.

Step 2: 
  • Drag the Calculated Field onto the "Columns" Shelf
  • Change the "Mark" Type to "Text"
  • Right-Click inside the Column -> Select "Format" -> Click the Icon that looks like a Pen, -> Remove "Zero Lines"
  • Right-Click on the Axis Header (which is ironically at the bottom) -> Select "Edit Axis" -> Change the Title to something reasonable
  • Still in the "Edit Axis" Window, open the "Tick Marks" Tab -> Select "None" and "None"
  • Drag the Desired Measure onto the "Text" Shelf
  • Resize the Column
Single-Column (Dual-Axis?) Crosstab
Now, you've created a "Single-Column Dual-Axis Crosstab."  Realistically, we aren't going to use the "Dual-Axis" component in this post; however, I'm not sure what else to call it.

Step 3:
  • Drag the "Field" Calculated Field to the "Columns" Shelf and place it beside the previous one
  • Next to the Word "Marks" above the "Marks" Shelf, Select the "Down Arrow" -> Select "Multiple Mark Types"
  • Now, you can cycle between your different fields
  • Drag another measure to the "Text" Shelf for the new field
  • Repeat "Step 2" to clean this column up as well
Dual-Axis Crosstab
Now, you can apply individual formatting to each column by cycling through them on the "Marks" Card.  For example, you can color the "Profit" Column by "Profit" and color "Sales" by "Sales."

Step 4:
  • Repeat the above process for adding any more measures you want
  • Add any dimensions you would like to the "Rows" Shelf
Completed Dual-Axis Crosstab
Now, you can see that all "Orange" values should be looked into.  While this is a very simple representation, there is so much more that can be done here.  Feel free to experiment.  There's much more information to be found in the link at the beginning of the post.  Thanks for reading.

EDIT:  Some readers have been asking for a workbook illustrating this concept.  Zen Master Jonathan Drummey has a very good workbook illustrating this technique posted here.

Brad Llewellyn
Associate Consultant
Mariner, LLC
llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

60 comments:

  1. Hi Brad currently I am using this make method to color individual columns but one of my client has come up with issue regarding crosstab.

    1)when we crosstab and copy the data the number of rows get doubled
    2)Sizing of row and column to fix is difficult.
    3)Is there any one formula where in I can use to format on decimal, color ,etc.

    ReplyDelete
  2. Bhujang,

    This is one of those methods that falls into the "workaround" category. There are many problems that can arise because of the use of this method. The best I can say is, each of your concerns is valid, and to my knowledge, there are no more solutions.

    Sorry that I don't have better news :(

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. Thanks for the response, Rob! Could you be more specific about your issue? Perhaps sending me a .twbx or mock up?

      Thanks,

      Brad Llewellyn
      Associate Consultant
      Mariner, LLC
      brad.llewellyn@mariner-usa.com
      http://www.linkedin.com/in/bradllewellyn
      http://breaking-bi.blogspot.com

      Delete
  4. Is there a way to get the headers to show up on the top of the grid instead of the bottom?

    ReplyDelete
    Replies
    1. Add [Field] to the shelf again and make it a dual-axis, the second axis will appear at the top.

      Delete
    2. Thanks for the tip, however now it layers the data so I cannot read what is in the cell. Is there a way bring the axis up as you state without any data in that layer?

      Delete
    3. Change the mark type of the unwanted field. Polygon and Filled Map seem to work well.

      Delete
    4. Thanks so much for your help!

      Delete
    5. http://public.tableausoftware.com/profile/jonathan.drummey#!/vizhome/conditionalformattingv4/Introduction I found this. This is so insane...

      Delete
  5. I am a new user but your help so far has been great and worked perfectly. I am having trouble getting the titles on top and if you could please help me with some specific step by step instructions that would be great. I have attached an image of what I have so far. Thanks for your time and help. [URL=http://imageshack.us/photo/my-images/441/44od.jpg/][IMG]http://img441.imageshack.us/img441/4125/44od.jpg[/IMG][/URL]

    Uploaded with [URL=http://imageshack.us]ImageShack.us[/URL]

    ReplyDelete
  6. http://img441.imageshack.us/img441/4125/44od.jpg

    ReplyDelete
    Replies
    1. 1) Drag Calculation1 to the Columns Shelf, placing it directly to the left of the pill that you would like to move the header for.

      2) Right-Click the pill that you would like to move the header for -> Select "Dual-Axis"

      3) The header should now be at the top. Rinse and repeat.

      Delete
  7. This works really well, thank you. I have one question though, after creating the empty field and adding it to the column shelf, when we drag the measure to the "text" box is there a way to drag a measure that is a calculated field? I want to add a measure that is a difference between two measures, I set it up using a simple subtraction of two measures formula.

    It is not showing me the right numbers when I use the calculated measure field, so I am wondering if I am doing something wrong.

    Thanks
    Anu

    ReplyDelete
    Replies
    1. There should be no issue with using a calculated field. If you would like, you can email me the workbook and I'll see what I can find.

      Delete
  8. Thanks for you help, this is a great blog and very helpful. Two questions:
    1) Regarding the headers and moving them to the top, I repeated, adding the "field" to a dual axis polygon and now I have headers on top and bottom (cannot hide just the bottoms) - Any ideas?
    2) I would like to sort the data from High to Low or vice-versa...the data will not sort. Is there something wrong with what I did?

    ReplyDelete
    Replies
    1. TBD,

      Thanks for reading! Here are my responses:

      1) As far as I know, there is no way to hide the bottom axis. In fact, I don't know if it's possible to ever hide only the primary axis on a chart. This is one of the limitations of this "workaround."

      2) If you clicked on the sort bars that sometimes appear on your graphs, then you likely tried to sort on your SUM( 0 ) field, which would be useless. You can achieve your sort by right-clicking on your dimension and selecting the sort option. This will open a window that will allow you to sort any way you wish.

      Thanks!

      Delete
    2. Thank you for the quick response. The sort works perfectly when clicking on the dimension and specifying the appropriate measure. No worries regarding the headers. Lastly, with the conditional formatting, any way to exclude the Total from the formatting since this is the dominating factor and should not be included.

      Thanks again.

      - Jeremy

      Delete
    3. Jeremy,

      That's a very reasonable request. To be honest, I don't know of a way to do that. It surprises me that totals work like that. Try posting on the tableau forums at http://community.tableausoftware.com/welcome and see if anyone can help. Let me know if you find an answer. I'm very curious about it myself.

      Delete
  9. Hi, Can I get Tableau help here.

    Regards,
    Dhananjay

    ReplyDelete
    Replies
    1. Sure! Send me an email at brad.llewellyn@mariner-usa.com

      Delete
    2. Brad,

      I created a workbook with the method you described and I noticed that I can add 2 measures of different types (with formatting) on each of the dual axes. For instance, a dollar value and a number on bottom axis and a % and a special format on the top axis. At any given time, I can add only 2 ' types' of measures. Is there any way to workaround this? As in, can I add a dollar value, a dollar value with 2 decimal places and a percentage on each axis?

      Thanks in advance!

      Delete
    3. I'm not sure I understand you. For the method in this post, the value on the axes is completely arbitrary. However, if we consider the broader scope of your question, this is what I think you are asking, "Can we have more than 2 axes on any chart in Tableau?"

      The answer is no. Tableau does not allow three dimensional charting in the spatial sense. However, you can have a two-dimensional scatterplot with a third dimension represented by the color and/or size of the points on the scatterplot. Does this answer your question?

      Delete
    4. Sorry that I wasn't clear, let me try to put it in a better way. I have a bunch of metrics like Sales ($ 200), Visitor Count( number), Margin rate (%), Sales/Visitor($ 20.34) and a ratio (2.1x). I need to show these metrics in one column and their values in the column beside it. Given that they all need different formatting, I cannot put them all into one column in the datasource (because then Tableau will require me to assign only a number or a dollar or any other format to the entire column).

      So what I am doing instead is putting all dollar based metrics into one column, % based metrics into another column and so on.

      Then, I create 2 calculated fields and make them dual axis. On each axis, I drag out 2 of the columns I created- say a number and a special format (1.2x) and place them on Label. On the other axis I drag out dollar and dollar with 2 decimals onto the label. That way, I can show different format types in the same column.

      However, when I introduce a 5th type, say %, I am unable to drag that out to either of the axis because each axis can hold only 2 measure pills.

      And I can't convert them all into a string because I want to use a color conditional formatting based on their value.

      I hope I've not confused you with my explanation.

      Delete
    5. Any chance you could provide a mock-up or .twbx file to illustrate the issue?

      Delete
    6. unable to attach a file here, can you provide an email address?

      Delete
  10. Sent you an email. Thanks Brad!

    ReplyDelete
  11. Hi Brad,

    Can you use this technique to show negative values in a column as Red?

    Nice blog by the way.

    Thanks

    ReplyDelete
    Replies
    1. Absolutely. In fact, if all you want to do is color a column red, you don't need to go through all of this hassle. You can just drag the field onto the Colors Shelf.

      Delete
  12. Hey Brad,

    Nice workaround for coloring.

    Is there any way to color 'Region' column text or background with different color in the same view which you have created.
    i.e. Central(Green), East(Yellow).....

    Appreciate your help.
    Thanks

    ReplyDelete
    Replies
    1. Sandeep,

      Thanks for commenting. In my example, Region is a row label. To my knowledge, there is no simple way to dynamically color Row Labels. However, if you were to add another column to the table, you could add ATTR( [Region] ) to the text shelf for that column and color that value. Then, if you hide the header for Region, you would achieve the desired result. Does this make sense?

      Delete
    2. Thanks for quick reply Brad,
      I tried your suggestion but I couldn't achieve the Region color without effecting other color values.
      May be I am doing something wrong. It would be great if you can provide the steps in details.

      Thanks

      Delete
    3. Sandeep,

      I'll do you one better. Here's a download link.

      http://www.speedyshare.com/bwZnM/Colored-Row-Labels.twbx

      I don't recommend things like this. But this just goes to show that it can be done.

      Delete
    4. Hi Brad,

      could you please share the workbook .. im looking for methods of implementing row label coloring.
      i havent really found any workarounds for this , so any pointers would be really helpful

      thanks,
      Achutha

      Delete
    5. Achutha,

      Zen Master Jonathan Drummey has a workbook illustrating this posted at

      http://public.tableausoftware.com/profile/jonathan.drummey#!/vizhome/conditionalformattingv4/Introduction

      Delete
  13. Hi Brad,
    I have three measures 1.sales,2.quotes,3.profit.. i want to show these measures in pie chart with percentages.is it possible?can u help me on this

    ReplyDelete
    Replies
    1. Anil,

      Thanks for posting. I'm sure it's possible. Could you be a little more specific or at least provide a mock-up or .twbx?

      Thanks!

      Delete
  14. Hi Brad,
    This is very interesting blog. I want to do the similar but with pie chart. I have simple data.1. Metric Data, Current Value, MTD (calculated), YTD (calculated) values which needs to be shown as green/red based on three measure Lo, Hi, Metric Value, if the Current Value > Hi or Current Value<Lo need to show it as red other case I need to green. same condition for MTD and YTD. Let me know if you can help me I can send a sample screemshot and sample workbook. The easily example is to consider the traffic light status report.

    ReplyDelete
    Replies
    1. I think I've got an idea of what you mean. Just for simplicity, can you send me a sample workbook?

      Delete
  15. Hi Brad,

    This is a nice blog. I have used the dual axis concept for getting conditional formatting for 3 metrics. Even after the assigning the color to show as Red, green and yellow, the color changes once I close and open the work book. Any idea what is the issue. Any suggestions would be of great help.

    ReplyDelete
    Replies
    1. Lakshmi,

      Thanks for posting! You ask a very interesting question that likely goes beyond your issue. For some reason, Tableau "forgets" which colors are assigned to what values. The only logic I can imagine is that the data somehow changes and Tableau resorts to a default scheme for some reason. I encourage you to post on the Tableau forums. If you find an answer you like, feel free to post back here.

      Delete
  16. Hi Brad,

    Can we shrink the size of the primary axis and change the color of the primary axis only???

    ReplyDelete
    Replies
    1. Thanks for posting! To be honest, I don't know how of a way to make the axis any smaller. That's one of the deficiencies of the workaround. Also, I'm not sure what you mean by the color of the primary axis.

      Delete
  17. Hi Brad,

    Thanks for writing all your tutorials! So much easier to understand than the tableau ones!

    Ive been looking through your tuts and tableaus but cant seem to find anything that would enable me to have a heat map per row.

    i.e. Say I have each series as the rows and the day in the columns, I want to visualise any drops or gains that are happening for each series (independently).

    This is what it would look like in excel http://tinypic.com/view.php?pic=1z1dbhi&s=8#.U3sCvvmSz2W

    Any help you can give would be massively appreciated! :)
    Thanks

    ReplyDelete
    Replies
    1. The graph that you just showed is what's known as a highlight table. You can create one easily by creating a crosstab of the data you want (series on the rows and days on the columns) and changing the mark to square. You can then label and color the squares however you want.

      Delete
  18. Thanks a lot for the quick reply Brad :)

    The issue specifically is that I cant get the high/low highlight per series/row. This is the stage Im at in Tableau, the colours are not restricted per row:
    http://tinypic.com/r/16jlqn8/8

    vs what I do in Excel
    http://tinypic.com/r/2nbsevb/8

    Where the red (low) and green (high) are used on each series so that you can see drops/gains in each series rather than the whole sheet as I have series with wildly different amounts.

    Is this possible?
    Many thanks

    ReplyDelete
    Replies
    1. It seems like you want to color each value based on how much it changes within each row. For instance, an increase from 5 to 6 would be green, while a decrease from 5 to 4 would be yellow or red.

      You can do this with a table calculation. I could help you more if you sent me the workbook. But try coloring the rows with a calculation like this

      ( [Value] - LOOKUP( [Value], -1 ) ) / LOOKUP( [Value], -1 )

      With Compute Using set to "Table (Across)"

      For more information on how the lookup function works, check out

      http://breaking-bi.blogspot.com/2013/04/using-lookup-function-in-tableau.html

      For more information on Compute Using, check out

      http://breaking-bi.blogspot.com/2013/07/introduction-to-table-calculations.html

      Delete
    2. Looking at it again, you might be comparing each value to the average value in that row (or a preset value). You could try something like

      ( [Value] - WINDOW_AVG( [Value] ) ) / WINDOW_AVG( [Value] )

      With Compute Using set to "Table (Across)"

      Delete
    3. Brad.....I love you :P

      Yep I was trying to base the colours on the average in each row and this worked perfectly!

      Thanks a lot, I really appreciate the help :D

      Delete
  19. Hi Brad,
    Can i get solution for this
    If we sort the data in a descending order, can tableau color the top five measures on its own without any conditional formatting ?
    And even if the data is not sorted, can tableau automatically color top 5 measure, again without conditional formatting ?

    ReplyDelete
    Replies
    1. Hi,

      Thanks for commenting. If you sort the data, then you can use the

      INDEX() <= 5

      calculation to color the top 5. If you don't sort the data, then you might have to play around with the RANK() function that is new Tableau 8.1. If the table calculations are not working as you'd hoped, perhaps this post will be helpful.

      http://breaking-bi.blogspot.com/2013/07/introduction-to-table-calculations.html

      Thanks!

      Delete
  20. This workaround worked perfectly for what I wanted to accomplish. However, instead of coloring my value by text, I want to color the cell using square with the value being visible. When I increase the size of the square, the color pushes the values right out of the cells and I'm left with color only. Any ideas?

    ReplyDelete
    Replies
    1. If you click on the "Label" button, you should be able to align the text in the center of the square using the "Alignment" option. Does this make sense?

      Delete
  21. Hi Brad,

    This is a nice work arround this helped me a lot in my dashabord. But I am facing one hurdle in this. The column name is at the bottom i am not able to bring this to the top as i am using three variables. Can you please help me here?

    Regards,
    Nandha

    ReplyDelete
  22. Hi Brad,

    Thanks for writing this blog post. I was wondering if you’ve encountered any errors when using this method that affects the color legends when published on Tableau server.

    Using the steps above, I created two columns (% Change to LY and % Change to Plan) that each have their own color legend to indicate whether the value is negative or positive. In Tableau desktop, the colors display correctly if the values are negative or positive, but when I publish to Tableau server the color stays one color regardless of the value.

    I’ve tried using different colors (thinking that the browser couldn’t interpret it), but the same colors are being used for a different visualization on the same dashboard.

    ReplyDelete
    Replies
    1. Hi,

      I apologize for my delayed response. I'm not sure I understand your question. The first thing I would try is selecting the "Include External Files" when you are publishing. Sometimes, there are pieces of your visualization that are stored on your local machine that don't automatically get published. Let me know if this works.

      Delete
  23. Hi Brad,
    This is so helpful. Thank you for sharing this.

    I have 4 measures in the table. The way you have color coded the 2 measures based on 2 color coding formats. Can I similarly color code for 4 measures with 4 different formatting?
    As I understand from here only dual color coding can be done, But still want to confirm. Can you please help.

    ReplyDelete
  24. Hi Brad, this method has helped me with a tricky problem! I have one question - I can't get the text in the columns to align, they are always centered and I need right align. I've right clicked and selected format and changed the alignment to RIGHT every where I can find in my worksheet and it still won't do it. Any idea how I can achieve this?

    ReplyDelete