How to Flexibly Format Decimal Places in Tableau

Round to Two or Four Decimals Based on Measure Values

See how to dynamically round numbers to two decimals when the value is greater than one and four decimals when the value is less than one. You’ll also learn a trick for displaying a star when a goal has been reached.

Calculated field that dynamically rounds decimal places:
IIF(SUM([Measure])>1,ROUND(SUM([Measure]),2), ROUND(SUM([Measure]),4))

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you how to do decimal formatting in Tableau. That might sound simple, but I’m going to show you not only how to do the default all or nothing number formatting in Tableau, but how to apply two or even three different number formats to the same measure.

For this example, we’re going to be using this data set that I’ll provide a link to in the related content. But this could be anything. I’ve just got a dimension and a measure looking at things 1 through 10. But notice on my Measure values that they have varying decimal places. And let’s say that for this particular use case, I’d like to show four decimal places when we have values less than one and only two decimal places when we have values more than one.

Let’s take a look at our default options over here in Tableau Desktop. If I were to make a quick table out of my dimensions and measures, it would start out looking like this. And we just saw in the underlying data that we’ve got varying decimal places. But Tableau, by default, is setting all of the values to have one decimal place. That is what is called automatic number formatting.

I can change that in a couple of places. First, I could right-click on the measure, hover over Default Properties, and click Number Format. That would permanently change the number format. At least, it would permanently change its default. So whenever it’s added to the view, it would start out in whatever number format I set there.

You can also set the number formatting on a per-sheet basis by right-clicking directly on the field that’s being used on the view and click Format. This will allow you to change the format of the values on either the axis or the pane. For this particular cross-tab view, we don’t actually have an axis. So I’m already in the right spot, and I can change the values per pane. It’s this third dropdown for numbers.

But, as I mentioned in the video introduction, this is all or nothing. So while I can customize it, it’s still all or nothing, and it does not fit my use case. I want four decimal places when the value is less than one, two decimal places when it is more than one. So far, all I could do is click Number Custom and pick how many decimal places. But, notice, it gets applied across the entire data set. So I could go with two or four, but I can’t have a mix of the two. That’s what I’m going to show you how to do next.

I’m going to change that back to Automatic for now, and I’m going to make a calculated field. In this first option, this will use the ROUND function. So I’ll call this the Round Option. This is the most elegant way to fit this exact use case. So that’s what I’m going to start with. It involves the Immediate IF statement– I’ll type IIF– because there are two outcomes.

My criterion is when the SUM of this measure is greater than one. So when that is the case, I want to round to two decimal places. So what I will do is type the ROUND function and then the SUM of my measure comma 2. Then I’ll type a comma.

And what comes after this second comma in this Immediate IF statement is what do I want to display when this statement is false. So when the measure is less than one, I want to round to four. So I’ll type ROUND again, SUM of measure, close the parentheses comma 4, close the parentheses, and then close the entire statement.

And this calculation got a little bit long on me. I will provide it in the related content below the video. But just to walk you through what this is doing, whenever there are only two outcomes– in this case, we have two outcomes because the value’s either greater than or less than one. Whenever that’s the case, I prefer to use this Immediate IF statement. With the Immediate IF statement, you start by typing your criterion. That entire highlighted area is the criterion. When the value– either the value’s going to be above one or not.

What comes after the first comma– so this comma right here– is what you want Tableau to display when that criterion is true. When the value’s above one, I’m rounding the measure to two decimal places is what that portion of the field is doing. What comes after this comma is what you want Tableau to display when the criterion is false. In this case, the value is less than one. When that is the case, I want Tableau to round to four decimal places. That’s what this portion of the calculated field is doing.

I’m going to click OK and replace my measure with Round Option. Let’s take a look at what happens. Nothing has changed yet. It’s because we’re still in automatic formatting. But now, if I right-click and click Format like I did before and change the numbers dropdown to Number Standard, we should see the mix of values appear. And we do. Notice, everything that is less than one is rounded up to four places. Anything that is above one is rounded to two. So here’s a good example– 0.15, 0.21.

So that is the most elegant way to do it. There is a slightly trickier way that gives you a little bit more flexibility. This next way that I’m going to show you, this next technique, will allow you to not only round decimal places but show completely different formats. This is useful if– on another video, for example, we show how to use parameters to select a certain measure. And it allows you to do more than one number format based on what measure is selected.

For this one, what we’re going to do is create a calculated field. And I will call this my Negative Option, because to get this one to work, we’re going to multiply certain things by a negative. So for this one– and I’ll also use a slightly different syntax for this one just to show you an alternative. I could use the Immediate IF statement. But I know that I’m also going to show you how to use this to take it a step further and do three number formats. So I’ll no longer have two formats. So let’s go with IF-THEN logic instead of the Immediate IF statement.

And I’ll say, if the SUM of the measure is less than one– so my values that I want to round to four– I’m going to multiply those by negative one, which is the same thing as just putting a negative sign at the beginning. Otherwise– so in this case, you’re greater than one. I’m just going to show the original SUM of measure. End.

So again, I’m showing this to illustrate this technique. I had to pick a use case. But this could be applied for any time that you want to apply more than one number format to a measure. What you need to do is multiply whatever you want treated differently by negative one. That’s what this is doing on the first row. In my case, what I want to treat differently are numbers that are less than one. I want to round those to four decimal places instead of two. So I multiplied those by a negative one. Otherwise, I’m just showing the original.

Let’s take a look at how this one looks if I were to add it to the view. I’ll put Negative Option on the view. And we’re back to automatic number formatting, because whenever you make a calculated field, by default, it doesn’t get any particular number formatting. It’s just called automatic. So we’re back to the one decimal place. And sure enough, whenever the value is less than one, it’s preceded by a negative sign because it’s multiplying it by negative one.

Now, watch what happens when I go to format this by right-clicking and clicking Format. This time, I’m going to choose Custom, which will allow me to customize values that are positive or negative. That’s what this last box does. So when it is positive– so those– and with my logic, that means all the numbers that are above one. When they’re above one, I want to show 0.00.

And then how this kind of special open custom formatting box works in Tableau is the first format that you type will be applied to positive numbers. If you then type a semicolon, the format that you type next will be applied to negative numbers. So in our case, our negative numbers are the ones that were multiplied by negative one, so the ones that were less than one.

For those, I want to say 0.0000 to get to four decimal places. And you can see it updating in real time in the background there. So we’ve just applied multiple decimal formatting options to a single measure with that little hack to multiply things by negative one.

A little bit more involved than the ROUND function, but it does have extra benefits. One big benefit is I can apply a third number formatting. If I were to type another semicolon in this box, whatever number or format I type next will be applied to NULL and values that equal zero. Just to illustrate, let’s say that for this use case I see a couple of outliers, things that are up in the 50’s. Here’s one for 100. Those are much larger than the others. Let’s pretend that if we hit the value of 50, then we met our goal. And we don’t really want to worry about those metrics anymore.

I’m going to show you how to apply a third number format. And to do that, I’m going to edit my Negative Option calculated field. And I’ll also change the name to 3 Number Formats. I’ll type an ELSEIF statement on this second line, and I’ll say that if the SUM of the measure is greater than 50– I’ll say greater than or equal to 50. So we hit our goal or above. Then I’m going to convert those to a zero, because, remember, that third number format is applied to zeros. So I’m converting my values that hit my goal to zero. I will click OK.

Notice that thing Seven and Six, which hit the goal, were converted to zeros with two decimal places temporarily. But now, if I go in and format this, I can type a third number format. So I’ll type a semicolon, and we’ll say that if you hit the goal, I want to display this star character. That’s an alt code character that I copied before the video. And I’m using it in this use case just to illustrate that we’ve passed the goal. Let’s not worry about those rows. Once again, you saw that update in real time below.

There’s two drawbacks to this. I’m going to show you how to solve one of them and just give you a heads up on the other one. But one drawback is we lose the actual value. It’s nice to have that third unique number formatting, but I don’t know how far above the goal we were.

So what I could do is simply add the original measure to the Tooltip Marks Card. And now when I hover over it, at least they can see what the original number was. So the star’s telling the user, hit the goal. Don’t worry about it. If you really need to see the raw number, you can hover over it and see it via the tooltip.

The one big gotcha with this final technique that I showed you is it only works with positive numbers, because, remember– let me take one more look at this calculated field– we are applying some logic to make some of the numbers negatives and some of them zeros. They’re not actually negatives or zeros. So if we really did have negatives or zeros in our data set, it would get this treatment.

So for example, if you had a row that really was zero, because of our number formatting that we just applied, it would give it that star treatment. And that couldn’t be further from the truth. Not only did we not hit our goal. We’re actually at zero. That’s probably our worst performance we could possibly do.

So that’s the one gotcha with this last technique. But the choice is up to you. There were three different ways to do decimal– or really number formatting in general– in Tableau.

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