My Favorite Tableau Trick For Work: MIN(1)

Many of us found +Tableau Software from the work that we do in our day jobs, and have since taken our passions to our free time.  When given the ability to build visualizations we're passionate about, people come up with creative solutions like Anya A'Hearn's Stairs Of The City, KK Molugu's Playoff Quarterback Touchdowns, Russell Spangler's Bacon Viz (and WAY too many more to mention).  

HOWEVER...

In our day jobs, there is much less room to be creative and more of a need to be direct.  As a result, designs usually involve displaying more numbers than visualizations.  And while there are best practices by Nelson Davis about when to use numbers and when to use visuals, numbers end up more often than us 'creative types' would prefer.  

FEAR NOT!

You can still let loose your creative side even when displaying numbers.  My personal favorite trick is the MIN(Number of Records) trick, or simply MIN(1).  You can create the field like so, and then set the default aggregation to MIN

You can create a new field or simply use 'Number of Records' that is in default Tableau

Set the default aggregation to Minimum to make ongoing use easier

And get results like this

Learn About Tableau

WHAT?!

MIN(1) is a calculated field that you can create that give you a measure in your worksheet that acts as a place holder for other measures.  You can then add whatever measure, or combination of measures, to the text charm of your field and display that in a very organized fashion.

???????????

An example then.

Normally, if we wanted to show a series of numbers on a sheet, we put 'Measure Names' in the rows, 'Measure Values' in the Text, and it would look something like this....

But if you want to color just the Profit Margin line, based on a set percentage of 20%, you can't.  An attempt would make your result looks something like this...

However, using MIN(1), you can set placeholders for each of the measures and then analyze them however you want.  Here's the same sheet, redesigned....

Notice how I've dragged four copies of 1 on the screen that show up as MIN(1) because that is the default aggregation I have set.  Every row is now on a scale of 0-2, with 1 dead in the middle, but more importantly it provides it's own measure to color, size, give level of detail, etc.  Now I can just color the Profit Margin line by itself and leave the other lines alone.

NOT ENOUGH, YOU SAY?!

What about combination charts, a chart that shows both bars and text.  If we try the traditional example again, we get something like this.

While we are able to achieve both text and charts, the text is aligning to the value it represents thus making it hard to read the numbers.  Now let's' rebuild this using MIN(1)

The result is that the numbers are all aligned for each metric, a bar chart is in the middle for the specific metric worth highlighting, they are still separate measures which allows us to edit color, size, and level of detail, with an overall clean look.

UPDATE:  Steven Carter wrote a post to do filled cell conditional formatting with text.  Forcing the cell lengths and using Gantt charts you can now have a look that is similar to Excel (for helping people transition away from it)

Way to go Steven!

In addition in both cases, because this is all done in 1 chart there is one query back to the data source, and if you have too many values to fit on the screen, you can SCROLL (!!!!!) and know that one scroll will move all of the fields necessary.

ONE MORE EXAMPLE

I was working with a customer where on a dashboard, we wanted a way to highlight the number of alerts that people needed to pay attention to.  To make it stand out, I put the number in a text box with a highlighted background and a border. 

Using MIN(1) as a bar chart and setting the range from 0 to 1, I can set the border on the bar chart, change the background color based on any value you want.  All of this is happening while also never having to worry about the bar chart range.  Here's what the sheet looks like.

CONCLUSION

If you're building a dashboard and need to display numbers in some capacity, MIN(1) is another tool in your Swiss Army Knife of Tableau skills.  It allows you clean layouts but with all of the customization we've come to know and love using Tableau. 

Please comment or tweet at me (@DanRMonty) with other ways you've found you can use MIN(1)!

DISCLAIMER: you do need to have text fields on your dashboard now to label your measures (you can put them in a container to make it easier to manage).  You can see this in the dashboard above.