Tableau 201: How to Dynamically Format Numbers

Create a string parameter with the measure options

This is the most flexible approach possible to dynamically formatting numbers in Tableau. It allows you to customize both the prefix and suffix for any measure and works whether the values are negative or positive. To begin, set up a string parameter with the three measure options:

Normalize the measure selected

Next, you have to create a calculated field that tells Tableau how to handle each parameter selection:

Note that in this case, I handled the discount measure differently than quantity and sales. I’ve forced the aggregation to be average and multiplied it by 100 so the decimal moves over two spots to the right (making it easier to read the percentages).

Now whenever this Measure Selected KPI is used in combination with the Measure Selection parameter control – which appears by just right-clicking on it and choosing “Show Parameter Control” – the end user will have the ability to choose between the three measures. This is a great user experience, but look what happens when the Sales measure is selected, for example:

Sales should be in currency format, but there is no dollar sign. That’s because all three measures in our Measure Selected calculated field have to share the same format and, by default, the format is set to automatic. We could change the format to currency, but then a dollar sign would be shown for the Quantity and Discount measures. This same problem happens when the Discount measure is selected because it is the only measure of these three that should display a percent sign.

Format numbers with prefix and suffix calculated fields

My solution involves setting up two additional calculated fields that are based on the parameter; one for the measure selected prefix, and one for the measure selected suffix. For these three measures, my prefix calculated field would look like this:

This formula is telling Tableau that if the Sales measure is selected, display a dollar sign; otherwise don’t display anything. Since the Discount measure needs a percent sign added to the end of the number when it is chosen, I will also set up a calculated field for the number suffix:

Now I will add both the prefix and suffix calculated fields to the Text Marks Card. After all of the fields needed are on the view, click into the Text Marks Card to edit the order that the fields are displayed. To get the measure selected to display properly with its respective format prefix and suffix, place the prefix calculated field in front of the measure selected field, and the suffix calculated field behind the measure selected field:

An Introduction to the Tableau Marks Shelves / Marks Cards

Now when the Sales measure is selected, a dollar sign is shown as the prefix:

When the Discount measure is selected, the dollar sign goes away and a percent sign is shown as the suffix:

When the Quantity measure is selected, no prefix or suffix is displayed:

We were using a basic view for the purpose of illustration, but this approach works in larger crosstabs, with mark labels, and tooltips.

Thanks for reading,
– Ryan

This content is excerpted from my book Practical Tableau: 100 Tips, Tutorials, and Strategies from a Tableau Zen Master published by O’Reilly Media Inc., 2018, ISBN: 978-1491977316. Get the book at Amazon.