Sometimes, when working with Tableau, you may come across the need to change the number formatting of a visualization. However, using Tableau’s built-in formatting options can lead to a common issue: all the values are formatted together. This means that if you have a value like 0.01 alongside a value like 10,000, and you want to format everything in thousands (K), Tableau will format both numbers as 0K and 10K, respectively. Unfortunately, this approach obscures the true value of 0.01 and hampers your ability to accurately interpret the first measure.

In this insightful Tableau Bites tutorial, we will implement a clever workaround that allows for dynamically changing the number units formatting using a parameter. By leveraging this technique, users gain the power to select which unit should be displayed, preserving crucial information that would otherwise be lost.

Let’s dive in and unlock the potential of this powerful feature.

Below you will find the final result of this tutorial as an interactive dashboard.



To begin, launch Tableau and either create a new project or open an existing one. In this tutorial, we will be using the Sample Superstore dataset as our example. If you wish to follow along or explore the final result, you can access and download the source file on Tableau Public.

Now, let’s determine which measure we want to dynamically change the units for. In my case, I will be focusing on the Sales measure. You can select a different measure that aligns with your specific requirements.

Once you have identified the measure, we can proceed by creating a parameter that will serve as the toggle for switching between different units.

Create a parameter in Tableau and configure it to include the following options, as depicted in the image below. Make sure to copy the values exactly as shown in the Value and Display As fields. Feel free to modify the values to suit your specific needs.

Unit parameter
Creating the Unit parameter.

With the parameter set up, we can now move on to creating the first calculated field required for our implementation. For this tutorial, I will name the calculated field ‘Normalized Sales’ but you can choose any name that makes sense to you.

Next, copy and paste the following code into the newly created calculated field:

CASE [Unit]
    WHEN 'K' THEN SUM([Sales]) / 1000
    WHEN 'M' THEN SUM([Sales]) / 1000000
    WHEN 'B' THEN SUM([Sales]) / 1000000000
    WHEN 'Default' THEN SUM([Sales])
    END

This Tableau calculated field uses a CASE statement to conditionally modify the calculation based on the value of the Unit parameter field we have created earlier. Here’s a breakdown of how it works:

  • When the value of Unit is ‘K’, the calculated field divides the sum of Sales by 1000. This effectively converts the sales values from their original unit to thousands.
  • Similarly, when Unit is ‘M’, the sum of Sales is divided by 1,000,000, converting the sales values to millions.
  • If Unitis ‘B’, the sum of Sales is divided by 1,000,000,000, converting the sales values to billions.
  • Finally, if the value of Unit is ‘Default’, the calculated field returns the sum of Sales without any conversion.

Essentially, this calculated field dynamically adjusts the unit of measurement for the sales data based on the value of Unit.

In order to complete the implementation and display the appropriate suffix for the dynamically formatted value (K, M, or B), we need to create another calculated field.

Let’s name this calculated field ‘Normalized Sales (Suffix)’ Copy and paste the following code into the newly created calculated field:

CASE [Unit]
    WHEN 'K' THEN 'K'
    WHEN 'M' THEN 'M'
    WHEN 'B' THEN 'B'
    WHEN 'Default' THEN ''
END

This code allows us to dynamically assign the corresponding suffix for each unit in the visualization. When the Unit field is set to ‘K’, the calculated field will return ‘K’ as the suffix. Similarly, ‘M’ will be returned for ‘M’, ‘B’ for ‘B’, and an empty string for ‘Default’.

By combining the ‘Normalized Sales’ calculated field from the previous step with this ‘Normalized Sales (Suffix)’ calculated field, we can effectively format the sales values according to the selected unit and display the appropriate suffix alongside the numbers. To combine both calculated fields in the visualization, we simply need to add them to the Text mark and arrange the fields in the desired order, like it is shown in the image below:

Image text order
Text mark order.

Now that we have the parameter and calculated fields prepared, it’s time to set up a visualization to observe the dynamic formatting in action. In this example, I have created a straightforward dashboard to showcase the functionality. You can explore and interact with this dashboard on the Tableau Public page associated with the project. Feel free to customize and adapt this method to suit your specific needs.

Default and Thousands view
Default and Thousands view.

Remember, the provided example dashboard is just a starting point, and you have the flexibility to adapt this method to your unique requirements. By leveraging Tableau’s powerful visualization capabilities, you can create compelling and informative displays that effectively communicate your data insights.

Final result.
Final result.

If you have specific doubts do not hesitate to reach me out via Twitter.