Multiple number format types of the same field in Tableau
Sometimes we need to apply multiple format types to the same field in order to display, for example, variable number format.
In real life I experienced such situation many times and Tableau do not offer simple build-in functionality to do this.
Let’s consider a common case when we have to parameterize metric in monetary and percentage values. Let’s take build-in data set “Sample Superstore” for demonstration.
So, the task is to create a report that shows monetary value of net profit and profit-to-sales % on the same view by using parameter for switching displayed content. It is fairly simple to do:
First of all, we need to create these fields.
We will use simple table for example, it shows us last 20 days of net profit and profit to sales ratio:
But notice the values of our metric, they show without any formatting.
For monetary values and percentages we see just floats. We can format “Displayed Metric Calculation” field, but what options do we have? Currency number format will give us what we want for “Net Profit” but “Profit to Sales, %” format then will be inappropriate, and vice versa.
Of course, in some cases we may leave it as it is, but if we are going to show this report for C-level management or investors, or maybe we have much more options in parameter and they are not self-explanatory that appropriate number format can help a lot.
So, the option here is to adjust “Displayed Metric Calculation”. Here what we need to use for this example:
These simple manipulation with values and conversion to a string make our metric’s number format very flexible so we can set it up like we need. This example is not going to get detailed instruction on how to do it for every case, it rather gives an idea about approach we can use to deal with such tasks.
Note that since the final calculation has string type values, we may need to adjust the view to represent information with charts: