Data Viz Done Right

# Tableau Tip: One Metric; Two Number Formats

Here's the scenario:
1. You have four metrics you want a user to be able to choose from.
2. Two of them are in number format (Sales & Profit) and two are in percentage format (Discount & Profit Ratio).
3. You want the data, when displayed, to accurately reflect the formatting of the metric selected
4. You only want to use a single metric.
To start, consider the above scenario by starting with this table:

Create a parameter that allows the user to pick which metric to display:

Next, create a calculated field to return the proper metric based on the selection in the parameter.

When this metric is placed in the viz and the selection is changed, the number formats aren't quite right. Sales and Profit look fine, but Discount and Profit Ratio, which are both percentages, aren't formatted properly.

Here comes the trick.  Create a slightly different calculated field to get the value of the metric selected:

Do you see what is different? There's a negative in front of Discount and Profit Ratio. Place this field in the view now instead, it's still not quite correct because Discount and Profit Ratio are now negative.

The trick is to change the default number format for this field. Right-click on the Metric Label field from the Measures window, choose Default Properties, then choose Number Format.

In the Default Number Format window, choose Custom and enter the string #,##0;0.0%.

Now the view will properly display numbers for Sales and Profit and percentages for Discount and Profit Ratio. This works because a custom number format allows you to specify a different format for negative numbers.

Here's an interactive version for you to play with:

NOTE: This formatting trick only works if all of the metrics will only return positive values. If Discount and Profit Ratio can return a negative value, then that will be converted to a number format (because negative x negative = positive). Similarly, if Sales can be negative (e.g., returns), then those would convert to a decimal format.

Here's an example of those scenarios:

1. This comment has been removed by the author.

2. I saw this post today and I pushed it a little :)

https://public.tableau.com/profile/publish/SayBiimi/Yazda#!/publish-confirm

Kar Oranı = Profit Ratio
Seçim=Select
Yazı Biçimi = number format

Because of you, I could think this :)

1. and thank you :)

3. Fantastic tip, just what the doctor ordered, thanks!

4. Hi Andy, another helpful article here. Thanks for contributing, and I do have a question here. I want to know how to achieve the layout exactly like the first pic showing on top. How to make a list of all these measure's together showing at same page instead of rotate them by the parameter selector? Is that coming from the parameter?

1. I would encourage you to recreate this step by step and also download the workbook. You'll see the answer along the way.

2. I got it, thanks.

5. What if you were filtering on Region already across multiple worksheets/dashboards and wanted to see the data filtered on a region instead?

6. Hi Andy, it is really helpful. but i have an advanced question like the one you solved. I have two KPIs(buyer, revenue), and i want to show like"\$ 100 M" or "\$ 100 K" for revenue and "12 M" or "23 K" for buyer when i filter to corresponding KPI. Hope you have time to answer this. Thanks, Darren!