How to Automatically Change Number Units in Tableau

Dynamically update display units from K, to M, to B

By default, number formatting in Tableau is all or nothing per measure, but in this video, Ryan shares a two-step trick to automatically display different formatting depending on if the values are in thousands, millions, or billions.

Calculated field that normalizes the Sales measure for thousands, millions, or billions:

IF SUM([Sales]) >= 1000000000 THEN SUM([Sales]) / 1000000000
ELSEIF SUM([Sales]) >= 1000000 THEN SUM([Sales]) / 1000000
ELSEIF SUM([Sales]) >= 1000 THEN SUM([Sales]) / 1000
ELSE SUM([Sales])
END

Calculated field for K / M / B suffix:

IF SUM([Sales]) >= 1000000000 THEN “B”
ELSEIF SUM([Sales]) >= 1000000 THEN “M”
ELSEIF SUM([Sales]) >= 1000 THEN “K”
ELSE “”
END

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you how to automatically change number units based on the value that is being displayed.

Number units are the little letter, the suffix, that comes after a value. A lot of times this is used with currencies. But it doesn’t have to be. It can also be used with integers or even floats. But the letters are usually K for thousands, M for millions, and B for billions.

Before I show you the trick on how to dynamically change what that suffix is, let me show you why this is needed. Let’s say that I have a brand new measure called Sales. I’m just using the Sample – Superstore dataset. But if you were to use this measure for the first time out of the box, Tableau wouldn’t necessarily know that that is a currency, first of all.

So if I were to drag this to the Text Marks Card, we would just see that number as an integer roughly $2.3 million. Make this a little bit bigger so we can see it.

Now, that is OK. I mean, I guess this is all relative. The values are all relative. Big data is relative.

But with a number like this, I might try to put this into units of millions, because it’s got quite a few places or units in that number there. We’ve got 1, 2, 3, 4, 5, 6, 7 values.

If I were wanting to make this more concise, I could change the units from nothing or ‘Automatic’ to millions. And you can do that by right-clicking on the measure and choosing Format. And because I’m currently modifying the formats of the number on the ‘Pane’, which is the view, I’m already in the right spot.

But watch what happens when I choose Numbers and tell Tableau that this is in fact a currency, and I want to put this into units of millions. And I’ll leave it at two decimal places. So far, so good. It looks pretty good.

But if later on, which is very likely, I want to change the level of detail from the entire file, which is what we’re currently looking at, to, say, something like Region. So if I drag Region onto the view, I’m not loving that M so much anymore, because none of the individual regions are above $1 million in sales. So they’ve been broken into these two decimal places’ worth. And it’s very confusing to try to translate that.

This central region is roughly $500,000. But now instead of an M, I’d more likely want to say 500 K. That’s what I’m going to show you how to do on this video. It’s a pretty easy trick. Because measure formatting in Tableau is measure-specific, and it’s all or nothing– so in other words, when we put an M as the suffix for that measure, that’s the format of that measure. It doesn’t dynamically change.

Because of that, I’ve got another video here at Playfair Data TV that shows you how to dynamically change number formatting regardless of the number type. So whether you’re using a percentage, currency, an integer, or a float, it will dynamically change the prefix and the suffix.

This approach is very similar, but there are two calculations. First, we need to normalize the data so that it fits the suffix that you are assigning to it. So if you are above, say, a million, we want to divide that number into millions. That’s going to be the first calculation.

The second calculation is the suffix to create the number unit; the K, the M, or the B. So two formulas. I will share this first one in the related content below the video. Actually, I’ll share both of these.

And again, as you’ve probably seen elsewhere on the site, I don’t like for you to sit here and watch me type, but I will explain what this is doing.

So as I mentioned, the first formula, it normalizes the data so that it fits the suffix that you are pairing it with. This first line is saying that if the SUM of Sales at the viz level of detail– that’s what happens when you aggregate it like this. So in this case, that would be Region is the viz level of detail.

When the SUM of Sales per Region is above $1 billion, I want you to take that SUM of Sales number and divide it by $1 billion. That just converts it to billions. So if I put a B after it, it makes sense. It has normalized it.

We then do the same thing for millions. So when the SUM of Sales at the viz level of detail is above 1 million, we’re going to divide it by 1 million to convert it to millions.

And then the last line is for when– or the last number unit, rather, is for when the viz– when Sales at the viz level of detail is above 1,000. We will convert it to thousands.

And then the last line is for everything else. So if you don’t hit at least 1,000, we’re just going to show the SUM of Sales. So it’ll be more like the automatic numbering that we saw, the automatic formatting that we saw to open this example.

But that’s the whole formula. I’m going to click OK.

The second formula creates the suffix. So I actually just copied the lines of logic, because it’s the same for each of the respective number units. But instead of normalizing each one, I created the suffix, the letter that goes with it.

So for billions, I want it to display a B. For millions, I want it to display an M. And for thousands, I want it to display a K. Otherwise, if the number is below 1,000, we just have a blank space. So there’s no suffix involved.

I’m going to click OK. And let’s take a look at this one. So I’m going to back this out and replace the default Sales value with our newly Normalized Sales value. So I’ll drop that on Text.

And the automatic formatting, it looks like, has converted this to millions, which is what we wanted. So we had this default Sales value is above 1 million, but less than 1 billion. So it’s converted it to millions. We’ve just got three decimal places instead of two.

If I want to set the default format of this, just like I did with the first number, I’m going to right-click and click Format. Go to Numbers.

And this one is a currency. So I’ll click Currency Custom. That gets us the prefix, that dollar sign that we want, because Sales Normalized is a currency. But the suffix is what we created in that second calculation. So we’re going to leave that blank for now.

I also like these two decimal places. So we’ll leave that as is. So we’ll call that good.

The next thing we need to do is add our suffix. So I’m going to add that to the Text Marks Card as well. By default, it just goes there at the bottom of the view. But now that it is on the Text Marks Card, we can click on the Text Marks Card and click this ellipsis to cut and paste where these things are located.

So the Display Unit Suffix, I’m just going to cut that and put it immediately after my Sales Normalized for Display Units.

Anytime you see the Apply button in Tableau, you can preview the change before you accept it. So if I click Apply, that’s looking pretty good. So that’s what I would expect at this point.

On row number 1, that’s the default Sales amount. On row number 2, that’s the newly normalized version. I’m going to click OK.

So far, so good. And you might be thinking, well, why did I just go through all that trouble? Here’s why.

Now I’m going to add the Region dimension again to the Rows Shelf. And let’s see how this looks now. So again, the first row is the default. The second row is the newly normalized version.

Notice on the second row it fixed it for us automatically. Instead of having that kind of challenging to translate version of 0.50 M, it normalized. It translated that number to units of thousands and added a suffix of K. And this will work for billions, millions, and thousands.

Let me get rid of this. Actually, let me get rid of the default Sales value for just a moment, and point out one big gotcha with this. So I love this technique. It’s very convenient. It makes it very readable for my audience.

So I’m a big fan of it, but there’s one gigantic pitfall. And that is if you are ever mixing number units, this can be very confusing, particularly when it’s used on a visualization.

Let me give you an example. Instead of looking at this by Region, let’s break this down by Ship Mode. So here’s a really good example.

This isn’t terrible yet, but if you were just to glance at this and not be paying attention to the suffix, so if you didn’t notice that Standard Class was in units of M and everything else was in K, you might think Standard Class didn’t do as well as the others. But most people are used to reading this in a text table like this. So I don’t think this is too bad.

Where this becomes really bad is when we convert this to a visualization. So if I were to make this a bar chart, and we’re not coloring, so I’ll just get rid of that. We did lose our suffix on that. But ignore this for just a moment.

I’m going to rotate this and show you the issue. So the values, when they are normalized, these first three are being divided by units of thousands, while Standard Class, which was actually the highest value– let me put the original Sales value on the label so you can see that.

So the Standard Class is the only one above 1 million. So it got normalized to units of millions. Its default Sales value was divided by a million, which made it the shortest bar. If you were to look at this at a glance in a vacuum, you would think that Standard Class actually did the worst when it did the best.

So that’s the big gotcha with this. But other than that, this is just another trick to help you automatically normalize units and dynamically change how they are being displayed. So I’m a big fan of it. It works particularly well if you’re just trying to convey units in a text table.

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