Friday, October 31, 2014

Conditional Formatting w/ MIN(1)

Dan Montgomery is officially my favorite vizzer right now, with his brilliant post yesterday on using MIN(1) as a place holder for multiple measures. Check out his blog post and then come back here to learn how you can use this trick for conditional formatting.

In my example I used my college football weekly (most weeks, not this one, sorry sports fans!) data set to show the average rank, points for and points against for each of the football conferences with a team that's been in the top 25 over the course of the season. Keep going after the viz to see how I did it.

The first step was to add 3 MIN(1) place holders in the columns shelf and conference in the rows shelf:

I used a simple text label for the average ranking, and then switched the marks to gantt bars for my next two columns. The trick here is to put MIN(1) on the size shelf for your gantt bars giving you an axis of 0-3 with a gantt bar from 1-2. To make this fit your window, just change the size of your axis to be fixed from 1-2. You'll also click over to the "Tick Marks" tab and check None for both options.

Now that you have your bars, add your measure of choice to both color and label. Here is what your marks pane should look like when you're done:

Make sure you center those labels and format accordingly! You can see in the example, that my two measures are actually using two different color scales (since more points scored is good and more points against is bad) to give the effect of conditional formatting for two different measures. There you go folks. Thanks to Dan Montgomery, I am going to have lots of happy customers!


  1. Any idea how I might maintain the conditional coloring but also hide certain conferences - i.e. hide "American" but have 43 still be the max for avg points scored so that all the coloring stays the same? Thanks this was really helpful.

    1. Hi George,

      If you open in Tableau Desktop you should be able to right click "American" and just click "Hide". To unhide it, you have to click the drop-down on the "Conference" dimension pill and click "Show Hidden Data".

      Glad this helped! Let me know if this solves your issue.