How to Dynamically Format Numbers in Tableau

3 ways to take command of Tableau number formatting

Learn how to (1) easily format positive and negative changes with the ▲ and ▼ alt code characters, (2) how to conditionally format both the size and color of text, and (3) how to dynamically format the prefix and suffix of selected measures.

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you several different ways to dynamically format numbers in Tableau. First, I’m going to show you how to generate an up triangle when there’s a positive change, a down triangle when there’s a negative change. Second, I’m going to show you how to color those triangles, based on those positive or negative changes, which is trickier than it might sound. And third, I’m going to show you how to dynamically change a number format from currency, to percentage, to integer, based on what measure is being used on a view.

To lay the foundation for the first two examples over here in Tableau Desktop, I’ve set up a couple of parameters. Just imagine that we’re in a business, and we’re looking at current month sales versus prior month sales. If you want to see how this looks under the hood, I’ve just got a period-over-period percentage calculated field that divides current sales by comparison sales minus 1.

That’s what’s creating this current negative 37.05% performance. The months selected on the view are February versus January. All right, so this is a pretty common callout number you might see on some of my dashboards. The 59,751 represents the current month sales or the current performance. And then, right next to it, I want to show either a positive change or a negative change in percentage terms.

The first dynamic formatting that I’m going to show you how to do is how to change that negative sign, and sometimes you’ll see a plus sign, to a down or up triangle. How I always explain formatting in Tableau, if you’re stuck in Tableau Desktop, and you can’t quite figure out how to format something on the view, I just talk out loud or think through, what am I trying to format?

Well, in this case, we’re trying to format the period-over-period percent measure that’s currently on the Text Marks Card, because we want to see an up or a down triangle there. So that’s what we’re trying to format. And then, I’ve got a hint on the Fundamentals track where I say, “if in doubt, right-click.”

So I’ll right-click on what I’m interested in, and usually there’s a hint in there about what to do with it. And sure enough, the third option down is Format. So this is how I might go about formatting if I were new to Tableau. I’ll show you one other way in a moment, but I’ll just click Format here. And then you’ve got two choices. Well, two primary choices.

There’s many more than two choices in here. But the primary choice is whether you want to format those numbers for the axis or the pane. Well, our view right now doesn’t have any axis on it, so we’re formatting the number on the pane. Those are– you can think of those as the number within the view. And then we’re looking for this third dropdown called Numbers.

So I’ll click on that, and click Custom. This is the trick to getting this to work. So there’s all these default number formats, but we’re going to click Custom, which will allow us to enter the format for a positive change or a negative change.

Notice first, when I went to Custom we got this kind of automatic number formatting, so our percentages went away. We’ve got way too many decimal places. But now we can type in what format we want for a positive change first. That’s what comes first.

And the secret to using that up or down triangle that so many people like to incorporate into their views is to use what’s called an “alt code character.” They’re called alt code characters because you generate them by holding down the Alt key while you type in a certain code to generate these special symbols.

The up triangle is made with the code Alt, 3, 0. So if I hold the Alt key, then click 3, 0, and let go, I’ve generated an up triangle. Next, I need to type 0, which tells Tableau to show the number, and then a percent sign.

That gets me– if there’s a positive change, as we see so far, it would show 0 or the number. In other words, no decimal places, and a percent sign. As you know, this first period of our period change was negative, so it doesn’t quite look right on the view.

We need to also tell Tableau, in this same box, what to display when that change was negative. How you do that is you type a semicolon next to the positive formatting, and then type the negative formatting. The down triangle is pretty easy to remember, if you can remember how to do the first one, because instead of Alt 30, it’s just Alt 31.

So I’ll just hold the Alt key, 3, 1, let go. We’ve got a down triangle. 0 and %. So now it looks correct. We knew the change was negative 37. We see it, see a down triangle. And then the 37% with no decimal places. Of course, if you’d prefer in your business to show decimals, you could go in there and type 1 decimal, 2 decimals, and you can see that that change is reflected on the view.

One thing I might do to clean this up from here is type a space between these triangles and their percentages, just to give it a little bit more breathing room. And then, last thing I’ll show you here is you also have a choice of what to display when the change is neither positive or negative. So either we’re even, or it’s null.

You could type another semicolon, and I’ll just try out a couple of dashes, and I’ll show you how that works in just a moment. But that’s it for now. I will also copy and paste those alt code characters into the related content below the video. I’m not sure how– if it works exactly the same way on Mac, so you could also just copy and paste these codes from the related content, put them into this box.

Let’s take a look at how this is working. I’m going to close the Format pane, and I’m going to bump the current month up to March instead of February. That had a positive month-over-month change. So we see the down triangle flip to an up triangle, and that the percent change was 116%.

And then lastly, because we coded what to display when the change was neither positive nor negative. If I bump this back to January, because we’re just looking at current month versus comparison month, we’re looking at, in this case, January versus January, we just see the two dashes, because there was no change to reflect.

So that’s the simple way to do it. It’s really nice. We got an extra– pulled in some custom formatting in here to Tableau. It’s dynamic. I really like that. However, this does have some limitations.

One is, that triangle is a little bit large, in my opinion, relative to the weight of that 37% that you see on the screen there. Unfortunately, that’s all or nothing. Because these are formatted in the same exact place, they’re going to be the exact same size font. And that triangle, just by its nature, is more dense and more bold. So it’s looking a little bit too heavy relative to the text right next to it.

The other disadvantage to this is, if we were wanting to dynamically color that percent change, based on whether it was positive or negative, that would also be all or nothing. So we wouldn’t be able to have two separate colors based on that period-over-period percent.

But I would rather have a negative change maybe be colored red and a positive change colored blue. So that’s the next thing I’m going to show you. It’s a little bit more involved, but it’s a trick that allows you to take this dynamic triangle formatting a step further, and also color code it.

It involves a series of calculated fields. I’m just going to do one for now to show you the positive change for time here. You could repeat these steps for a negative change. But I’m going to click Create Calculated Field, and I’ll call this my Positive Change Triangle.

And there’s going to be two outcomes in this formula. If there is a positive change, I will display an up triangle. If there’s a negative change or anything else, because there’s only one other outcome, I’m just going to display a null.

Whenever that is the case, I recommend that you use the Immediate IF statement to write this calculated field. The formula is the function “IIF,” and I’ll open up the parentheses. And we’re saying that if Current Sales is greater than Comparison Sales, so there’s the criterion. Whenever that statement is true, Tableau will display whatever you type after that first comma.

So that alt code character is technically text, so it’s string. So I’m going to type an open tick mark to open up the string, and then my Alt, 3, 0, and let go, and then another tick mark to close the string, comma. What comes after the second comma is what you want to display when that first outcome is false. In this case, we’re just saying NULL, and then close the parentheses.

There’s my Positive Change Triangle. I’ll click OK. Now that I’ve gone through the trouble of thinking through that logic, it’s very easy for me to create the percentage piece of this, because I can just duplicate the first calculated field, make a couple of tweaks to it. This will be my Positive Change Percent.

And instead of showing the up string triangle, I’m just showing that Period over Period Percent Change. And I will click OK. Now that I have both of those formulas, I can drop them onto the Text Marks Card. So I’ll put that on Text, put my triangle as well as my percentage on the Text Marks Card.

Note that we didn’t see it show up yet. And that means it’s working, because these two things, which should only show up when the change is positive, and between February and January it was negative. If I bump this up to March, we should see both of those show up. So the up triangle shows up, and then our percent shows up.

But it’s in not quite the right format. So I’m also going to show you another way to format the number itself. I showed you how to right-click on a measure, and open the Formatting pane. But you could also make this slightly more permanent by right-clicking directly on the measure, hovering over Default Properties, and clicking Number Format.

And you could go to Custom, type in those alt code characters. But for this one, it actually is a percentage, so I will do Percentage with no decimal places and click OK. And now we see that matches the formatting from above.

Now that that is there, I can open up the Text Marks Card, and copy and paste these wherever I would like into the view. So I’ll cut this, maybe get rid of the original that was there. So there is my percent. I’ll put the triangle in front of it, maybe put a space.

But here’s the point with this. These are now individual fields on the Text Marks Card, meaning that I can format them independently of each other. Let me click the Apply button to show you a couple of options. So one thing I can do is make the triangle a little bit smaller than the percent sign.

But of course, this is a case-by-case basis. You’ll want to try it out. But this second technique that I’m showing you is just going to unlock some flexibility in how you’re formatting these callout numbers. So I just reduced the size of that Mark to 10, click Apply. And you can see, I can make these changes independent of the other.

I might want to make a positive change turn a green or a blue. So I’ll make this blue, click Apply. And just imagine the possibilities with this. I don’t want to bore you with all the individual steps.

But now that I have created the set for positive changes, if I also went ahead and duplicated those and created a set for negative changes, I could put both of those on the Text Marks Card as well. Those would also be formatted independently from each other.

So if I put the negative set right next to the positive set and colored them red, when there is a positive change, only the positive triangle and positive percent change would show up. It would be formatted blue. So we’re conditionally formatting based on the period-over-period change.

When the change is negative, the positive ones go NULL, because of those Immediate IF statements. It flips to the negative triangle and the negative percent change. And those can be conditionally formatted red. So it unlocks a lot of flexibility. Just a technique to take your callout numbers a step further.

I’m going to cancel out of that and move over to my third and final example. So for this one, I have a different foundation. What I’ve done is I’ve used one of my very favorite tactics, which is to allow my end user to choose measures and dimensions in Tableau.

In case you’re not familiar, I’ll quickly show you under the hood, but you can find this on another video. It’s just saying that, from this parameter control, I’ll go ahead and show that also. If you select Sales, I want Tableau to show the SUM of Sales.

If you choose Discount, it’s going to show the average discount. It’s multiplied by 100 to create the illusion that it is a percentage. We’ll cover that in a future step here on this video. And then, when Quantity, we’re going to show the SUM of Quantity.

I love this tactic. You’ve probably heard me say this tactic, or mention about 10 different times throughout Playfair Data TV. However, it does have one big drawback, which is the number formatting, once again, just like the first two tips, is all or nothing. So we can’t just pick currency for Sales, because if they chose Discount or Quantity, it would also show up as a currency.

Well, that’s what I’m going to show you how to do. It’s actually a very simple trick, just another creative application of some fundamental concepts. Instead of using the measure, I’m going to duplicate that pill or that measure. And I’m going to set up a dynamic prefix and suffix for whatever is selected.

So this first one will be my dynamic prefix. And all we’re doing is telling Tableau what letter we want to display before the number, based on what measure is being used on the view. So when it’s Sales, instead of SUM of Sales, I’m going to type, tick mark, because a dollar sign is technically a string, dollar sign, close the tick mark. There’s the first prefix.

When Discount is selected, there should be no prefix. So I’m just going to type two tick marks, open the string, close a string. And same with Quantity.

So what this logic is doing is it will display a dollar sign before currently what says $2.2 million on the screen. But if Discount or Quantity is selected, it will show no prefix. We’re creating the illusion of a number formatting. So there’s prefix. I’ll click OK.

Second thing I need to do is duplicate the prefix and create a dynamic suffix. So this is the letter that will go at the end of the measure being shown. When Sales is being shown, I’m going to show no suffix. When Discount is being shown, because that should be in percent terms, I’m going to type a percent sign. And when Quantity is being shown, we will show, once again, no number formatting. Click OK.

I need both of these, the prefix as well as the suffix, to be on the Text Marks Card to get this to work. So I’m going to drag Prefix to Text and Suffix to Text. Because Sales is the current allowable value in this parameter control, notice– so Sales is being shown. We see the prefix, just like we coded, but there is no suffix, so it’s NULL.

I’m going to clean this up a little bit by clicking on the Text Marks Card, clicking this ellipsis to open this word processor. And now I can cut and paste the prefix before the measure selected and the suffix after the measure selected. So I’ll highlight this whole thing, cut it, paste it.

In case you’re relatively new to Tableau, anytime you see that gray shading, that means that this value is going to be dynamic, based on selections or marks on the view. So right now, this entire widget, which has three different fields in it, is completely dynamic.

The prefix is based on this measure selected. The value itself, so the number, is based on the measure selected. And so is the dynamic suffix. I’ll click OK. When Sales is selected, we see a dollar sign, the value, no suffix.

If I flip this to Discount, the prefix of the dollar sign goes away. We see the value followed by a percent sign. And if we click Quantity, which just should be in terms of an integer, we see neither a prefix or a suffix. We’ve just dynamically formatted the number formatting, based on whether we are using Sales, Discount or Quantity, which all had different number formats.

This has been Ryan with Playfair Data TV – thanks for watching!