Optimizing Data Presentation: Formatting Numbers in Tableau

Tailoring Numeric Display: Methods for Formatting Units in Tableau

Simplifying Data Interpretation: Strategies for Number Formatting

When looking to measure a calculated field, number formats are very important for reporting, especially when representing huge numbers like population, stock volume, balances, manufacturing units, etc.  

Further to this, formatting (number format) is a feature of best practices since end-users are not interested in every decimal place; units, and tens place. They are more worried about the Billions and Millions and Thousands. Also, this does not inundate the end-users with a bunch of numbers. 

Unfortunately, Tableau does not have the same formatting features and is not as flexible as Excel in some cases. 

Using Custom Number Formats in Tableau

Utilizing Calculated Fields for Unit Conversion

Applying Number Formatting Across Various Data Sources

Enhancing Data Clarity with Intuitive Numeric Display

For this example, we will use a very simple 12-row data set ranging from the Units place to the Hundred Billionth place. 

Using the same data in Tableau.

Customizing Number Formats for Enhanced Visualization

Converting Units to Simplify Data Interpretation

Ensuring Consistency in Numeric Display Across Dashboards

Incorporating User Preferences into Number Formatting Techniques

We can see that the end-user might not be interested in reading a 10-digit or a 12-digit number. Following best practices, it is always good to represent formatted numbers. But the only problem with Tableau is that it performs Global formatting i.e. the entire field will be formatted (default format) into one unit, leaving no room for a custom format – resulting in a unit looking like: 

In these cases, everything less than a Million does not convey anything. This situation is fine if the end user only focuses on high values. But showing 0s kind of does not solve a good purpose of metrics.

Based on this, the end user’s requirement is to show the Billions with a B, Millions with a M, Thousands with a K and the rest of the values as is.

Method 1 – Format Numbers in Tableau to Required Units 

This is the most widely used method that uses String Calculations. It seems very simple. But the method is very laborious and time-consuming. But once the Calculation is ready, it can be used for every such occasion. 

Before we jump into the Calculation logic, here are some drawbacks of this number formatting technique. 

a) This field will no longer be numeric 

b) It cannot be used for any type of mathematical or statistical operations 

c) Sorting a Dimension using this field is extremely difficult 

d) Alignment of numbers can also be a problem 

e) When the value reduces or increases in the future, the Calculation might need to be altered a little 

Since this is a Calculation, we need some functions. 

1) Aggregate function – SUM(), AVG(), MIN(), MAX() etc. as suitable for the visualization in Tableau Dashboard. In our case, we shall use SUM(). 

2) String function – STR() is a type conversion function. It changes a field from a numeric data type to a string data type 

3) Length function – LEN() finds the number of characters in a string field including all special characters. 

For example, USEREADY – Reading from the left-hand side, every character is assigned a number. So, the length of this word is 8. 

Let us take another example: United States of America – We have totally 21 alphabets and 3 spaces. So, the total length is 24.

4) Left function – LEFT() reads from the left-hand side of a string and isolates the required part based on the number of characters.

For example: If we use the left function with USEREADY and specify 4, it will isolate the first 4 characters.

5) Right function – RIGHT() reads from the right-hand side of a string and isolates the required part based on the number of characters.

For example: If we use the left function with USEREADY and specify 5, it will isolate the last 5 characters.

6) **Round function – ROUND() reduces the number of decimal places for the value specified. This function is used only if the measure that we are using is a decimal data type. When the number is a decimal, converting it to string will add too many unwanted decimal points. To remove the decimals, we shall have to round it to 0 decimal places.**

This example will be solved in two phases. The first phase is very simple and straightforward. It will be just looking for the numbers that make up the Billion or Million or Thousand without the decimals.

Phase 1

1) Aggregate the required measure using SUM() function

2) Convert the aggregated measure into a string using STR() function

3) Find the number of characters in the aggregated measure using the LEN() function. Here the length function will not consider the commas as they are part of the number and not treated as extra characters.

The Revenue Buckets field is used to make it easy to interpret the segments that can be used for cutoff.

4) Next we need to isolate only the required number of characters using the LEFT() function and concatenate with the required units. An example is shown here for Billions. The same can be replicated for Millions and Thousands. We need ” ” (quotes) for special characters and the + (plus) operator for concatenation.

As explained before, we are counting the number of characters and picking only those that are required. Now the entire Calculation looks like this along with the $ symbol:

** If we need the negative values also to be formatted (Like Credit, Ratio, Inventory, Difference etc.), the “–“ is considered a character. So whatever we do for positive values will be repeated with one extra character.**

Phase 2

Now for the difficult part. The end users might need one or two decimal places. This is where we need the RIGHT() function. The initial steps are same.

1) Aggregate the required measure using SUM() function

2) Convert the aggregated measure into a string using STR() function

3) Find the number of characters in the aggregated measure using the LEN() function

4) Next we need to isolate only the required number of characters using the LEFT() function, followed by the required number of characters after the first comma using the RIGHT() function and concatenate with the required units. An example is shown here for Billions. The same can be replicated for Millions and Thousands. We are going for 2 decimal places.

We can observe that this calculation has 2 pieces. The first piece like the previous Phase is only picking the required number of characters before the decimal. The second piece uses the Right function along with the Left function to isolate 2 characters after the decimal.

Method 2 – Format Numbers in Tableau to Required Units 

This method will focus on the features of Measure Names and Measure Values and the formats. The new fields that are being created will still be measures. Some drawbacks of this method are that like the previous method, it is time consuming & repetitive and displaying headers will be a challenge. But it will preserve all the measure features and can be used for quick ad-hoc calculations.

Here again the focus is on Revenue buckets. We have to divide the original measure into required Units based on the buckets as a conditional measure value not as number of characters. In this example, there are no decimal measures. So, splitting them into conditional buckets is easier. If decimals are present, we have to take them into account.

1) Creating individual measures for each bucket. In this example, we will have 4 conditional measures.  One for Billions, one for Millions, one for Thousands and one for remaining values.

** Again, an important thing to note. If we have negative numbers, the process continues for the negative scale**

2) Next step would be to show all measures together.

All the measures created can be put together on the Text card or can be put into a single bucket of Measure Values. Both will work the same.

After using all the measures on the Text card, the text card features need to be edited. Placing all the measures right next to each other.

3) The last step is formatting the numbers to their respective units.

Most of us make a mistake of formatting the Measure Values field. It will not work. We need to format the measures individually.

As discussed before, showing the header is a little difficult. It has to be mentioned in the title. There are other hacks to achieve it which will be explained in other blogs.