Dynamic Field Formatting in Tableau

The Problem

A common requirement when building dashboards in Tableau is the ability to swap measures in a chart. This can be easily implemented using a calculation similar to the one below that returns a measure based on a parameter selection:

CASE [p.Select Measure]
WHEN “Profit” THEN SUM([Profit])
WHEN “Discount” THEN AVG([Discount])
WHEN “Days to Ship” THEN AVG([Days to Ship])
END

The problem is that each of these measures will likely require different formatting, for example: 

• Profit – displayed in thousands with a $ prefix and two decimal places
• Discount – displayed as a percentage with a % suffix and one decimal place
• Days to ship – shown as an integer with no suffix or prefix

While formatting can be applied to this dynamic measure, it cannot be dynamically updated based on the parameter selection. So Discount could end up formatted as a $ amount or your Profit formatted as a very large percentage:

Workarounds

As always, the creative Tableau community provides various workarounds.  A few such workarounds are linked below:

• Using multiple calculations – from The Information Lab
• Using Measure Names – from The Data School
• Using Sheet swapping – from Evolytics

Below are three possible solutions that would remove the need for these workarounds:

Solution 1 – Inherit formatting of the referenced measure

Default formatting can be applied to any measure in the data model. When one (dynamic) measure is returning another measure, the default formatting of the returned measure could be applied. 

This is not as straightforward as it seems, however, as what’s returned in these dynamic measure scenarios is not always a single measure. It could be the result of multiplying different measures together, for example, a measure to forecast profits increasing by a user defined percentage, such as: 

WHEN “Profit + N%” THEN [Profit] * [% Increase]

Should the dynamic measure inherit the formatting from the [Profit] field or from the [% Increase] field? One resolution here is to move that Profit * % increase calculation out to a separate calculated field and apply default formatting to that calculated field which could then be inherited within the dynamic measure field. 

Solution 2 – Add a format field option in the formatting panel

Tableau recently added the ability to create dynamic axis titles by using the value of a parameter or calculated field:

A similar approach could be used for field formatting. Currently, custom formatting can be applied to fields using formatting strings (see here and here for a deep dive into how this works). In addition to typing these codes in, they could also be sourced from a calculated field or parameter with a UI similar to that used for dynamic axis titles:

A calculated field, e.g. [Dynamic Formatting] could then be used to format our dynamic measure seen earlier. This formatting calculation might look like this: 

CASE [p.Select Measure]
WHEN “Profit” THEN “$ #,0,.0K”
WHEN “Discount” THEN “0.0%” 
ELSE “0”
END

This also opens up the option to dynamically change the formatting by updating the Dynamic Formatting calculation with a parameter if needed.

Solution 3 – Add a FORMAT() function

Perhaps the easiest solution is simply to have a FORMAT() function that would allow formatting to be applied to a field inside a calculation. The syntax for such a function could be:

FORMAT( <measure>, <format string> )

Our dynamic measure calculation might then look like this:

CASE [p.Selected Measure] 
WHEN “Profit” THEN FORMAT( [Profit], “$ #,0,.0K”)
WHEN “Discount” THEN FORMAT( [Discount], “0.0%”
WHEN “Days to Ship” THEN FORMAT( [Days to Ship], “0”)
END

Dynamically Format Colour

As a bonus, it would be nice to also format the colour of the resulting measure based on an additional argument to the FORMAT() calculation: 

FORMAT( <measure>, <format string>, <colour> )

For example, to show positive profits in blue text and negative profits in red text, we could update the first line of the CASE statement to be: 

WHEN “Profit” THEN FORMAT( [Profit], “$ 0,,”, IIF([Profit]<0,”Red”,”Blue”))    

Hex codes or RGB could also be used.

This does open up questions of precedence vis-à-vis encoding that might be applied to the colour shelf but this could be resolved, for example, with a fourth <override> argument where 1 = override colour shelf encoding and 0 = colour shelf encoding takes precedence. This would also allow for this setting to be dynamic based on a parameter.

No doubt there are other dependencies but it seems worth considering.

Current Ideas Forum Links

Custom formatting is a much requested feature and you can add your vote to one or more of the below ideas on Tableau’s Ideas Forum if you would like to see one of these implemented.

Idea 1 – Option 1 – Inherit field formatting
Idea 2 – Option 1 – Inherit field formatting
Idea 3 – Option 3 – Add a Format Function
Idea 4 – Option 3 – Add a Format Function

I couldn’t find an existing idea for option 2, but #1 and #3 seem like better options to me anyway, so I won’t add another idea to the already long list of live ideas on the forums.

Thanks for reading,
Marc

Latest YouTube video
This video tutorial steps through how to create a dot plot in Tableau.