Introduction to Dynamic Field Formatting
Enhancing Data Visualization with Dynamic Formatting
A rather frequent requirement when building dashboards in Tableau is the facility to swap measures in a chart. This can be fulfilled simply by using a calculation like 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 "Sales" then SUM([Sales])
ENDThe Challenge of Dynamic Chart Measures
The challenge is that each of the above measures will likely need 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
- Sales – displayed in thousands with a $ prefix and two decimal places
Adapting Formatting to Parameter Selections
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 as shown below:
(Continuation to Method 1 from my previous Blog)
Method 2 – Add a format field option in the formatting panel
Creating Dynamic Field Formatting with Calculated Fields
Changing Formatting Dynamically with Parameters
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. In addition to typing these codes in, they could also be sourced from a calculated field or parameter with a UI like 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"
ENDThis also opens the option to dynamically change the formatting by updating the Dynamic Formatting calculation with a parameter if needed.
Stay tuned for Method 3 in my next blog post.
