By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
You probably know that you can only set one number format per field in Tableau. That means that if you create a dynamic measure driven by a parameter (check out this blog post for details) you have to choose a number format. You could choose percent, decimal or currency, but you can’t get the format to swap.
I am going to start with this framework for my worksheet:
Let’s say I’d like to be able to compare Sales (currency), Quantity Sold (standard integer) and Profit Ratio (percentage).
First, I’ll create the parameter with the three options.
Next, I’ll create the calculated field that swaps between the three measures.
Notice how the axis and number format looks okay when Quantity Sold is selected.
However, the chart doesn’t look as great when Profit Ratio is selected.
Here’s how we can fix that.
First, let’s multiply the Profit Ratio of the Dynamic Measure calculation by 100.
Now, we need to create Prefix ($) and Suffix (%) fields.
Only the Sales field has a prefix.
And only the Profit Ratio field has a suffix.
Now it’s time to put all these pieces together.
First, we can remove the axis label.
Next, we can insert the parameter into the title to make it dynamic.
Then we’ll add the [Prefix], [Dynamic Measure] and [Suffix] fields to the Label section of the Marks card.
The labels will look weird at first, that’s okay. We can edit the label by selecting the Labels section of the Marks card and selecting the ellipsis.
By putting all three fields on the same line, we can build cohesive looking labels.
And the best part is, because we never altered the number formatting, it’s automatic so it will show decimals for Profit Ratio but not for Sales!
Thanks for checking out this post! As always, feel free to reach out with questions or book an office hour to work together!