We often get questions on how to do conditional format (like the spreadsheets do) – 

First, Tableau is not a Spreadsheet – see  Link BGS 1  if you need confirmation) –  In Tableau there is a data table for each worksheet. It is created by dragging dimensions to the rows and columns of shelves. The table is then filled with values as measures are added to the sheet. The “value” can be formatted first as numbers and colors can be applied (either continuous or discrete) 

Dimensions also have values that are used to classify the data ( Think like “Furniture” is a value for the dimension “Category.”   Discrete color can be applied based on dimension values – also dimension values can be counted and color can be applied based on that count –

Confusing?  Let’s first see how to apply number formats to a measure value:

1 – Formatting Numbers

We will use this data from the SuperStore Dataset – The default format in the text table is OK but I would like to see Sales as $K and Quantity in thousands.

That is done by formatting the Measures – the Formatting can be reached in different ways –

Right-click the green sales pill in the Data Frame – then Default Properties and finally Number Format is my go-to

Alternatively, you can select the sales green pill in the viz – then Format – then the Format frame opens and you can select the dropdown arrow.

Whichever way you choose open the number formatting window.

There are a series of default formatting options to select from – I chose Currency Custom – Note Tableau knows from the installation setup that I live in the US, so the currency is in $ –

Then you can select the number of decimal values – I only want to see one. But I  want to change the output units to Thousands, so I selected that from the dropdown and finally, I  want comma separators for long numbers.

I did something like Quantity and the result looks like this.

It is formatted the way I wanted but not exciting –  Let’s add some color.

2 – Color Formatting

Colors are either discrete or continuous as determined by the dimension or measure to which they are applied.

Applying color to a discrete dimension or measure will apply a discrete color palette – You can customize the palette by opening the Color Card

First, you can select a pre-configured palette by picking from the dropdown. If you still need to edit,  select an individual value and change its color to another on the right (single click)  -or you can double-click the value open the advanced color editor, and make your selection there.

Placing a continuous measure on the color tile will create a continuous color palette.

The editor allows you to select from various palettes to fit your viz.

These can be customized in the “Edit Color” window – – like reversing the high – and low ends.

Continuous colors also can be converted to discrete “bins” by selecting the number of steps and using the advanced option, setting the min and max values and the center point. (it is a particularly useful way to set the color of negative values to red and positive to black )

OK – let’s move on to examples.

3 – Adding Color to a text table

We can start by dragging the Measure Names onto color – that does make it easier to separate the Quantity from the Sales – but doesn’t visually tell us much about their relative values.

Placing Measure Values on the Color Tile is better and if we had only a single measure in the chart would be the way to go.

But, the same color range applies to both the Sales and the Quantity, and it does not provide useful insight into Quantity. This is where Conditional Formatting comes in

4 Conditional Formatting

There can be multiple approaches to conditional formatting.

The easiest way takes off from where we were is to “Use Separate Ledgers.”

Each Measure Value will have its own Ledger–

When the edit window opens you can select a different pallet from the dropdown menu, and, as shown here – convert from a continuous scale to a discrete scale – I chose five, but you can use what best fits your needs.

For the example, the result would look like this: 

Now the Quantity and Sales have distinct color palettes – the palette on Quantity is discrete and is set at 5 levels – and the palette on Sales is Continuous over the full range of Sales.

Each is “dynamic” in the sense it adjusts to changes in the data table and filters applied.

5 Advanced Conditional Formatting

Let’s see how to use multiple axes to create the same chart-

You may have noticed when you place two measures on the column shelf, each measure creates its own viz – then you use “Dual Axes” to make that a single “common viz.

You can use that same approach to produce any number of axes. Since each axis has its own marks card, it can be formatted independently. Let’s start by recreating the chart we just did above.

Here I used sum(0) twice on the column shelf to create 2 axes each with its own marks card

Each marks card can have a different detail – here I use a Square chart type on each – Quantity on  one and Sale on the Other and then edited the color tile as before

Now you can get creative.

Each marks card can have its own type of calculation – here the Percent of Sales in each segment and the total sales are presented side by side in each with its own conditional color format.

A common request is to combine graphs and text in alternating columns – not easily done but it can be accomplished with multiple axes.

Here there are four axes used to show Profit, Rank, ASP, and Sales  – Each is controlled on its own marks card – — now the beauty is that the chart Is dynamic.

Here expanded to sub-cat and sorted by profit in the region – while rank is overall.

Or here dropping in the year of order date

6 Formatting Headers, Totals and Subtotals

Headers, totals, and subtotals do not automatically format like the rest of the chart – but it can be done-  

First, we need to create a discrete dimension that will replace the “Row Headers”  – The dimension will contain the Text values we want as the row header.  Easy for the detail rows but not the Total or Subtotals. For that we need to use a custom

if min([Category])<>max([Category]) then "Grand Total"

elseif  SIZE()=1 then "Subtotal " + attr([Category])

elseif min([Category])=max([Category]) then attr([Sub-Category])

end

This just places “Grand Total” on the total line (that is where the first min/max condition is met – next for the Subtotal rows where size()=1 use “Subtotal and the category value, and everywhere else use the Subcategory values calculation (  for the detail explanation on how it is done see Link to totals)

Now to create the viz

We need three fake axes on columns – use sum(0) – and on rows will need the real Category and Subcategory dimensions to bring the data into the data table – (we will hide the first two columns later)

The first fake axis Marks card is a little complex – the Real Category dimension goes on Color –the Category label calculation goes on Text – it is the values we want to see as our Fake Header and it is a table calculation (that is a result of the Size() function – so we need to change the way it calculated to Restart every time the  “Real Category” changes (i.e. Furniture to Office Supplies)

The other two marks cards are just the sum of the measure (Sales or Quantity) on Text and the Real Category on Color

Hiding the Category and subcategory dimensions yields the final result  – the color coding extends onto the fake Header –

Now I do not recommend using continuous color, but someone will ask – can it be done with a continuous color palette – – yes it can – Subtotal and Total rows are aggregated – out of the range, and are grey.

I hope that helps you the next time you need to use custom formatting in your viz.

All examples here can be found in  Download Workbook Here

Jim