Innovative Tableau

Chapter 65. How to Dynamically Change Number Units Between K/M/B

This chapter shows you how to automatically change number formatting in Tableau between no units for values less than one thousand, K for thousands, M for millions, and B for billions. Number display units are a great way to clean up visualization labels or to save real estate in a text table if you are looking to maximize the data-ink ratio described in Chapter 12.

The issue is that number formatting is all or nothing per measure, so if you set the formatting to display units of K for thousands but then have a value of one hundred, Tableau will display 0K. Not anymore—there’s a hack for that!

How to Automatically Change Display Units for Thousands, Millions, and Billions in Tableau

Because formatting is per measure in Tableau, I’ve previously covered how to dynamically format measures, even when your field requires more than two number formats (i.e., integers, currency, and percentages).

Note

Related: Practical Tableau, Chapter 65, “How to Dynamically Format Numbers” (O’Reilly, 2018)

The trick to dynamically changing display units between none, K, M, and B follows a similar approach, but instead of a calculation for the number prefix and suffix, you need a calculated field that normalizes each measure and a second calculated field for the suffix (which becomes the display units).

If you want to dynamically change display units for the Sales measure in the Sample – Superstore dataset, the first formula would be ...