Dynamic Units of Measure in Tableau

Beginner
30–45 minutes
Tableau Desktop
June 9, 2020

Which is easier to read?

$1,234,567,890.00 or $1.2B?

When someone is scanning a dashboard, they shouldn't have to count commas to understand a number.

Dynamic units make large numbers easier to read by automatically displaying values in thousands (K), millions (M), or billions (B), without changing the underlying data.

In this tutorial, we'll explore four ways to use dynamic units in Tableau:

  1. Create dynamic KPI labels.
  2. Format values in text tables.
  3. Add color to highlight positive and negative values.
  4. Preserve exact values in tooltips.

And we'll address one important detail that's easy to overlook: negative numbers.

Image preview

‍

Let's get into it!

‍

Create Dynamic KPI Labels

Dynamic formatting is a popular Tableau trick, but there's one important detail that's often overlooked: negative values.

Many examples use logic like this:

IF SUM([Sales]) >= 1000000 THEN "M" END

That works beautifully for positive values. But what happens when Sales is -1,000,000?

It returns a null value because -1,000,000 isn't greater than or equal to 1,000,000.

The solution? Use ABS() to evaluate the absolute value.

IF ABS(SUM([Sales])) >= 1000000 THEN "M" END

Now the calculation recognizes values of either sign. The original value can remain negative while the formatting logic determines the appropriate unit.

Keep your numbers numeric

Personally, I prefer keeping measures numeric rather than converting them into strings whenever possible.

For KPI labels, we can create separate calculations for each magnitude:

  • Billions: Values of at least 1,000,000,000 in absolute magnitude.
  • Millions: Values from 1,000,000 through 999,999,999.
  • Thousands: Values from 1,000 through 999,999.
  • Hundreds or less: Values below 1,000.

Don't forget to assign the appropriate custom number format to each field!

Here are the calculations for the numbers:

Calculated field: Hundreds or Less

IF ABS(SUM(Sales)) < 1000 THEN SUM([Sales]) END

Calculated field: Thousands

IF ABS(SUM([Sales])) < 1000000 AND ABS(SUM([Sales])) >= 1000 THEN SUM([Sales]) END

Calculated field: Millions

IF ABS(SUM([Sales])) >= 1000000 AND ABS(SUM([Sales])) < 1000000000 THEN SUM([Sales]) END

Calculated field: Billions

IF ABS(SUM([Sales])) >= 1000000000 THEN SUM([Sales])END

Notice that we're returning the original numeric value, not a string.

Finally, place all four calculated fields on the same line in Text on the Marks card.

Image preview

‍

Because only one magnitude calculation returns a value at a time, the label displays the appropriate formatted number.

Why use this approach?

It keeps the calculations numeric and avoids unnecessary string conversions. The tradeoff is that we need multiple calculated fields to handle the different units.

Tip: For units Tableau doesn't offer automatically, such as kilograms (kg), you can add the unit through the Suffix option in Custom Number Format.

Image preview

‍

This same approach can be adapted for currency, time, weight, volume, area, and length.

Format Dynamic Units in Text Tables

KPI labels are great, but what happens when we need to display multiple measures in a table?

Imagine a year-over-year analysis with three columns:

  • Current Year
  • Previous Year
  • Change

We want the values to display dynamically, with the appropriate units, without forcing readers to interpret long numbers.

For this scenario, we can create calculated fields that combine a rounded value with its unit.

Unlike our KPI approach, this method converts the formatted result into text.

Here's an example for Sales:

Calculated field: Sales Display

IF ISNULL(SUM([Sales])) THEN ""
ELSEIF ABS(SUM([Sales])) >= 1000000000 THEN STR(ROUND(SUM([Sales]) / 1000000000, 1)) + "B"
ELSEIF ABS(SUM([Sales])) >= 1000000 THEN STR(ROUND(SUM([Sales]) / 1000000, 1)) + "M"
ELSEIF ABS(SUM([Sales])) >= 1000 THEN STR(ROUND(SUM([Sales]) / 1000, 1)) + "K"
ELSE STR(ROUND(SUM([Sales]), 0)) END
Example of Value + Unit Calculated Field for Current Year Sales

This gives us results such as:

  • 1,250,000 → 1.3M
  • 25,000 → 25.0K
  • -2,500,000 → -2.5M

Repeat this pattern for all of the measures you want to display.

Image preview

Build the table

Image preview

Place your formatted calculations in Text on the Marks card and arrange them in the desired order.

Image preview

‍

Because these calculations return strings, we can't use them exactly like regular numeric Measure Values.

Image preview

We can instead create a calculated field containing our measure labels, using line breaks to align the headings with the values.

For example, a label might contain:

Current Year
Previous Year
Change

Image preview

‍

Place the label field on Rows and adjust the text formatting until the labels and values align.

Image preview

‍

Tip: Null values can create awkward spacing. A simple calculated field containing " " can help maintain alignment when a value is missing.

The result is a compact table with readable numbers and clearly identified measures.

‍

Add Color to Positive and Negative Values

Now that our text table is working, let's make it a little more Tableau-y!

Color can help readers quickly distinguish positive and negative changes, especially when we're displaying year-over-year performance.

One way to achieve this is to split a formatted measure into two calculated fields.

Calculated field: Negative Values

IF SUM([Sales]) < 0 THEN [Sales Display] END

Calculated field: Positive Values

IF SUM([Sales]) >= 0 THEN [Sales Display] END

Place both calculations on the same line in the Text label.

Then format the negative values in red and the positive values in a neutral color, such as black.

Only one calculation will return a value for a given mark, so the appropriate color appears automatically.

You can also use the space-padding technique from the previous section to maintain alignment.

One important consideration: Red doesn't always mean bad, and positive doesn't always mean good. Choose colors that reflect what the measure actually represents.

For example, an increase in revenue may be positive, while an increase in expenses may not be.

Preserve Exact Values in Tooltips

Dynamic units make numbers easier to scan, but they also introduce rounding.

A displayed value of 1.2M might represent 1,180,000 or 1,240,000. Those differences can matter!

That's why I recommend including the original, unrounded measure in the tooltip.

For example:

Displayed value: $1.2M

Tooltip: Sales: $1,234,567.89

To do this, add the original measure to the worksheet's Tooltip and format it with the desired number of decimal places.

This gives readers the best of both worlds: concise labels for quick interpretation and precise values when they need more detail.

You can even add conditional color formatting to the tooltip if it helps communicate the meaning of the measure.

Image preview

‍

Bringing It All Together

Dynamic units are a small formatting improvement that can make a big difference in how people experience a dashboard.

The key is choosing the right approach for the situation:

  • KPI labels: Keep measures numeric with separate magnitude calculations.
  • Text tables: Combine rounded values and units into formatted strings.
  • Positive and negative values: Use conditional calculations and meaningful colors.
  • Tooltips: Preserve access to the exact underlying numbers.

And remember to use ABS() when evaluating magnitude so your formatting works with negative numbers, too.

The goal isn't just to make numbers shorter. It's to make them easier to understand.

Happy vizzing! 💗

‍

Say hello!

Have a question or just want to say hi?
I'd love to hear from you.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
×
This is some text inside of a div block.