Friday, December 11, 2015

Cell Level Formatting in Tableau

I have to start this post be saying 'Please dont treat Tableau like Excel'.  They are different tools designed for different purposes.  Excel is a spreadsheet-making tool.  Tableau is a data visualization tool.  If this is your only use case for Tableau then you are doing it wrong.  This technique should be used only when absolutely necessary.

Cell-level formatting is a challenge in Tableau because whenever you introduce more than one measure into a text table you need to use the Measure Names and Measure Values fields.  They are like 'bucket' fields that contain all of your measures in one.  If you are confused by these read this KB article.

The problem with that is you only get one Marks card for Measure Values, which means only one Color shelf, so you cannot add a different field on Color for each of the Measures you are including (they will all get colored by one Field).

So what do we have in our bag of Tableau tricks to get us multiple Marks cards?  Add more measures to the Rows or Columns!  Each measure you add to Rows or Columns creates an axis, and in turn each axis gets its own Marks card.

We will use this to our advantage by creating an axis of '1' for each column we want to have.  You can use the Number Of Records field, or you can create a new Calculated Field and just stick '1' into it:


Use the MIN of this field and add as many of them to the Columns as you need for your view (1 field = 1 measure/column).

Then change the mark types to Text.

Then choose the Marks card label for the first MIN(One) and add your measure to both Text and Color.  Repeat for each measure you want to have a column for.

Now we just need to tidy things up.  You can make the columns skinnier by just adjusting the line that separates the axes on the bottom, and then right-click on an axis (or one of the measures on Rows) and uncheck 'Show Header'.

Now you might be thinking 'What about the Labels..?!' this is where we use a dashboard and some floating text objects to label each column for us.  Drag in a Text object, make it 'floating', and then drag and dop exactly where you want it to go.  Rinse and repeat for each label.


Tip:  use the area in the lower left to adjust the pixel values for  'y' and 'h' to align them all perfectly.
Tip 2:  use a fixed-size dashboard so your labels always stay exactly where you placed them.  If you use Automatic sizing, then the relative placement of your fixed and floating objects will change and you label placement will get messed up.

Booyah.  Cell level formatting in Tableau.

Note:  In this example I use Text for all mark types and I color each column.  There is nothing stopping you from making some columns Shapes or Highlight Boxes (using the Bar mark type) or not coloring some columns at all (I will include an example in the workbook you can download).  This is example just shows the framework which you can extend upon.

Workbook: https://app.box.com/s/cz4zjhnei5hnhtxyepg7e9fj9vxjf3oo

-The Don

Special call out to the O.G. Scott Wasserman for figuring out this genius use of multiple mark cards when tableau introduced Dual-Axis charts way back in the day.  Floating objects in dashboards further simplified the original workaround which required far more steps to get labels for the columns.  Maybe some day we will be able to use Measure Names and Measure Values in calculated fields and this workaround wont be needed anymore, but until then, use it but dont abuse it.



4 comments:

  1. Just wish to say your article is as surprising.

    The clarity to your post is simply great and that i could
    suppose you’re a professional on this subject.
    Well with your permission let me to clutch your feed to stay up to date with approaching post.

    Thank you a million and please continue the rewarding
    work.
    Tableau Guru
    www.sqiar.com

    ReplyDelete
  2. Hi there! I've seen this 'hack' several times, either using MIN or ATTR. But what I just can't manage to do is to format the alignment of the data in the cells. For example, I would like to have all numbers right-aligned, just as in the regular text table. But none of the various formatting options seem to do the trick. Any help would be greatly appreciated!

    ReplyDelete
  3. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Tableau.
    MaxMunus Offer World Class Virtual Instructor led training on Tableau. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Nitesh Kumar
    MaxMunus
    E-mail: nitesh@maxmunus.com
    Skype id: nitesh_maxmunus
    Ph:(+91) 8553912023
    http://www.maxmunus.com/


    ReplyDelete
  4. Great Article. its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    Tableau online training

    ReplyDelete