Online tableau training, tableau tips, & video tutorials
How to Dynamically Format Numbers in TableauPreview
Automatically change formats between integers, percentages, and currencies
This video shares how to dynamically format measures in Tableau. We will be using the Sample – Superstore dataset to dynamically format the Quantity, Discount, and Sales measures into integer, percent, and currency formats, respectively.
Hi. This is Ryan with Playfair Data TV, and in this video, I’m going to show you two different ways to dynamically format numbers in Tableau. The first method I’m going to show you is how to show an up triangle or a down triangle depending on if the value is positive or negative. The second technique I’m going to show you will unlock even more flexibility. You’ll be able to dynamically format the numbers not only for if they’re negative or positive, but for what type of data is being used on the view. This is a common challenge whenever you parameterize your measure selection. Because if you allow your end user to choose between, for example, sales, which is a currency amount, profit ratio, which is a ratio or rate with a percent sign, and say an integer such as quantity, the challenge is Tableau only allows you to choose one format per number, but I’m going to show you a tricky way around that.
But for the first method, we’re going to use alt code characters to add an up triangle or a down triangle. Alt code characters are special characters that show up on a PC when you hold down the alt button and type in a combination of numbers. You can find these in a lot of places on the web. I also have a blog post over at my personal blog, RyanSleeper.com. Part of the reason I wrote this, to be honest, is so that I had a repository for some of my favorite alt code characters.
So I’m going to go to this page, and right there in the intro, you can see two of my favorite alt code characters, the up triangle and the down triangle. I’m going to go ahead and copy this first one, and go back over here to Tableau. For the convenience of this illustration, I’ve just got this parameter with allowable values of all. So you can type in any integer that exists. And I’m doing that just so I can type in quickly some negative numbers and positive numbers.
I’m going to format this number on the view, which is just on the text marks card right now. So as you can see, if I change the one to a two, that’s what shows up on the text marks card here. I’m going to format that number by right clicking on the measure and choosing format. We’re trying to modify the format of the numbers on the pane, which is the numbers on the view. So we’re in the right spot. I’m going to navigate to the drop down, the third drop down, for numbers and choose custom.
The trick here is whatever characters you type first is what will show up for positive values. So if I paste this alt code character, because that number two is positive, that triangle shows up. But I can also type a number sign to get my integer back. So any positive number will show up with a upward triangle and a number sign. I’m sorry. And the value. If you type a semicolon, that allows you to tell Tableau what to display when the value is negative. So for this value, I’m going to go back to my blog post and grab the down triangle, copy that, go back to Tableau, modify my custom numbers, and I’ll paste the down triangle as well as numbers again. And now, if this number were to change to a negative, make sure that stuck, if this number were to change to a negative two, we would see the down triangle and the value of two.
You also have a third option if the value’s neither positive or negative. So either if it’s null or if it’s zero, if you type another semicolon, you’ll have one more opportunity to format the numbers. Let’s say for zeros or nulls, we’ll just put a dash. And now, if my integer were zero, we would just see a dash. That’s my favorite technique when I only need to format the numbers whether they’re going up or down.
This next way I’m going to show you is a little bit more involved, but it’s very flexible. To start with, I’m going to switch the number formatting back to automatic. And again for convenience, I’ve set up a parameter, which allows my end user to choose from three different measures. I’ll cover this technique in a different video if this isn’t familiar to you. But for now, I’m simply showing you how we’re going to display a different number formatting based on the measure that’s selected in this parameter.
So I’ve set up these three choices. The data type is string, and it’s got these three allowable values, sales, profit ratio, and quantity. Over here, I’m going to set up– I didn’t show you this behind the scenes on the first approach, but we’re basically just saying no matter which of those three measures are selected, we’re going to show an integer.
But here’s where this new formatting option comes into play. We’re going to build two more calculations that will display a dollar sign when the value selected is sales, a percent sign as the suffix when the measure selected is profit ratio, and no number formatting when the value selected is quantity. So I’m actually going to copy this, because this is most of the logic that I need. And this trick involves simply setting up two additional calculated fields. So I’m going go over here and create a calculated field. One is for the prefix. So I’m going to call this one prefix. Most of the logic is the same, but we’re simply going to give Tableau instructions for what to do when each of these three measures are selected.
And remember, this is the prefix or the format that comes preceding the number. So when it’s sales, we want to show a dollar sign. When it’s profit ratio, we don’t want to show anything. So I’m just going to do two quotes, which will just be an empty string value. And then for quantity, same thing, empty quotes. So it’ll just show a blank string as the prefix. Click OK.
Now that we’ve got the prefix, I’m going to duplicate that calculated field to create the suffix. So I’ve duplicated that calculated field. I can now edit it, because the logic is very similar. We’ll call this the suffix. This time, this is the value or the character that comes after the value. So for sales, there’s no formatting after the number. So I’m going to make that blank. When profit ratio is selected, we want to display a percent sign. And when quantity is selected, once again, we want to show nothing. I’m going to click OK.
Now that we’ve got both the prefix and the suffix, we’re going to add those to the text marks card. I’m going to drag prefix to text and drag suffix to text. You’ll notice a dollar sign show up. That’s because sales is the current value of this parameter. And I can double check that by right clicking on the parameter and clicking show parameter control. So this is a good way to spot check if everything’s working. So when sales is selected, for the prefix, we should see a dollar sign, and for the suffix, we should see nothing. So far so good. If I select profit ratio, I should see nothing for the prefix, and a percent sign for the suffix. And if I select quantity, I should see neither.
Now that I know it’s working, I’m going to click the text marks card and align these properly so that it’s kind of faking the format of this number. So I’m going to click the text marks card. Because there are three things on the text marks card, which is indicated by these icons, I can click this 3 period icon, which will open a little word processor, and I can simply reorder these so they make sense. So I’m going to cut the prefix and paste it in front of the value. And then I’ll cut the suffix and paste it behind the value. Clean this up. Click OK.
And now, as I make my selections, let’s give us a more realistic number like 500. So 500 is the integer. When it’s quantity, there is no number formatting displayed. When it’s sales, you see a dollar sign preceding the value of 500, but nothing after it. And if you click profit ratio, you see the value, and then a percent sign.
This has been Ryan with Playfair Data TV – thanks for watching!
Related video: How to Make Dynamic Bump Charts in Tableau
Related video: Shaping Data for Use with Tableau
Related blog post: Tableau 201 – How to Dynamically Format Numbers in Tableau
Related chapter: Practical Tableau – Chapter 65 – How to Dynamically Format Numbers
- How to Dynamically Format Numbers in Tableau
- How to Conditionally Format Individual Rows and Columns in Tableau Like You Can in Excel
- How to Allow Users to Choose Dimensions and Measures in Tableau
- Exercise: Sales by Category, Region, Segment, or Ship Mode
- Using Tableau’s INDEX() Function for Easier Top N Filtering