It’s been way too long since I’ve written a blog post, and this one will be a relatively simple one. But sometimes it’s the little things that make that critical amount of difference in being able to communicate with your data and achieve peak user happiness. 🙂
I recently put together a dashboard at work that required some specific conditional formatting that I hadn’t done before. The solutions ended up being quite simple, but given that many of my colleagues hadn’t considered them before, I figure there may be others out there who would benefit from them as well. They’re all mini-hacks that let you have a bit more control over how Tableau displays your data.
1. Conditional Number Formatting
The Situation: you have a measure in your view and you don’t want to stick to the default formatting since the numbers can be quite huge (e.g. $5,528,202,691) and therefore either overwhelm the view with unnecessary specificity or result in hash marks (e.g. ###) in tables on smaller screens. You could just format it to be shown in millions or billions, but then you run into other issues:
- When you filter or drill down to a more granular slice you might find yourself with a number that is pretty useless (e.g. $0.0B).
- Math – while it isn’t hard math, it can still be annoying to have to convert $0.34B into $340M in your head, especially for busy executives.
The Solution: create two (or more) versions of the measure, based on a volume threshold, and format each differently. There are ways to do this inside of a single calculated field by leveraging String functions (and Andy Kriebel has a great post about that) but this alternative requires much simpler calculation logic. The downside is that you’ll have a few more calculated fields, but certainly not a crazy amount.
It’s critical not to include an Else condition in these calculations since you want the measure to return Null if the condition isn’t met. You’ll see why soon.
Next you’ll want to set the default format for each of these new measures. Select the way you want each one to display (e.g. billions in #B, millions in #M, etc.)
Finally, just place your different measures onto your Labels or Text shelf (depending on whether you’re using a chart or a table). You will want to edit the Label / Text shelf to ensure that the measures are displayed side-by-side (vs. stacked on top of each other, which is what Tableau will do by default). Since only one will ever produce a result (and the others will produce Null), only one of the values will ever display in your chart. And the one displayed will be formatted correctly.
2. Formatting Total Bars Differently from Non-Total Bars
The Situation: you have a bar chart that contains sub-totals and you’d like to visually distinguish the totals from the rest. So, instead of seeing this version of the chart:
You’d like to see this version:
The Solution: use a trick Joe Mako showed me many moons ago that uses MIN and MAX functions to identify whether a mark is a total or not.
In the case of the chart above, I want to know whether each mark represents a Month (vs. a Total).
Total = min(DATEPART(‘month’,[Ship Date])) = max(DATEPART(‘month’,[Ship Date]))
This will return a True for the Months and a False for the Totals. The reason this works is because the Total marks won’t return a value for either the MIN or MAX functions, whereas the Month marks will.
You can then just place this field onto the color shelf and select which color you want for True and for False.
3. Line Labels – Using a different value for the Start and End points
The Situation: you want to only label the ends of a line graph, but you want to label one side with a volume and the other side with a text label (e.g. customer, region, product, etc.) The default labeling functionality lets you place both values on both line ends, but not one on each.
The benefit of only showing one value on each end is that it still emphasizes the latest data point (arguably the more critical of the two end-points) while also minimizing the reliance on a color legend, since the name of the dimension is attached to the line. In addition, having only one value on each line end gives your chart a better shot at remaining readable in a smaller space since you won’t have as much text competing for the same real estate.
The Solution: create two new “display” fields that use the INDEX function to determine where to show a value.
- if index()==window_max(index()) then sum([Sales]) end
- if index()==1 then attr([Product Segment]) end
As with all Tableau calculations, you can either hard-code these specific values in (e.g. Sales and Product Category) or use a parameter to make that more dynamic based on user selection.
You can download the workbook below to further explore any of these formatting solutions.