10 Replies Latest reply on Oct 2, 2018 1:36 PM by Ken Flerlage

# Rounding trailing 0's in decimals

Hello,

I was wondering if anyone knew if it was possible to fix a problem I have. Is it possible that if a mark label is rounded to one decimal place (for example: 5.4 and 3.0), is it possible to have it keep 5.4 as a decimal, but if a number ends in a .0 then it gets rid of it so that it just shows the number 3 rather than 3.0? As of now I haven't been able to find any answers to this and I'm not 100% sure it can be done in tableau, but it would be really helpful if anyone had any insights for it.

• ###### 1. Re: Rounding trailing 0's in decimals

Try using the following custom number format: #,##0.#;-#,##0.#

The # after the decimal point will indicate that it should show that first decimal if it exists, but shouldn't show it if it does not exist.

• ###### 2. Re: Rounding trailing 0's in decimals

Thanks Ken, this is great! One thing as of now though is that with that, if it ends with the 0, it'll show up as "3." rather than just "3". So it adds the character that is the decimal there no matter what. Is it possible to hide that as well?

• ###### 3. Re: Rounding trailing 0's in decimals

Hmmm, yeah that's true. Can you try just using "Number (Standard)"

• ###### 4. Re: Rounding trailing 0's in decimals

This is close! It does get rid of the 0 and decimal which is good, but in cases where it is 2 decimals it keeps that. In this case the 2.25 and 3.75 is the average of a few numbers. Does the Number (standard) format possibly have a custom formula for it anywhere?

• ###### 5. Re: Rounding trailing 0's in decimals

Hi Yosef - does this video post from Andy Kriebel help?  Using the Modulo Function to Conditionally Format Numbers - YouTube

Thanks, Katie

• ###### 6. Re: Rounding trailing 0's in decimals

Okay, can you try creating a calculated field like this then using that to display your numbers?

// Number would be rounded down to a whole number

ELSE

// Round to nearest 1 decimal place.

END

• ###### 7. Re: Rounding trailing 0's in decimals

Ken, I think this is the right track. I've been messing around with it and in a few cases it currently won't work. Let's say for example the actual average is 2.25. Based on the way the formula is now, it would do IF 2.0 - 2.25 <0.5, which in this case is -.25 and thus is <0.5 and so it is true, causing 2.25 to round to 2 rather than to 2.3.

I think we would just need to modify this formula a little, just trying to think of how exactly.

• ###### 8. Re: Rounding trailing 0's in decimals

That's right--ROUND was the wrong approach in the IF statement. Let's switch the order a bit and use INT instead, which will truncate the decimal value.

// Number would be rounded down to a whole number

ELSE

// Round to nearest 1 decimal place.

END

So, in this case, the first number will always be larger than the second (or the same)--it will never be negative.

1 of 1 people found this helpful
• ###### 9. Re: Rounding trailing 0's in decimals

Ken Flerlage thank you, you beautiful soul. It worked!

(And as another part to the thank you, I remember I've used the tutorials on your blog so many times and I immediately recognized your name hahaha)

• ###### 10. Re: Rounding trailing 0's in decimals

My pleasure! And I'm so happy that you've found my blog helpful!!