5 Replies Latest reply on Nov 9, 2018 2:11 PM by Mavis Liu

# Understanding how Round() function handles 5's

Hello,

I'm trying to figure out how to best use the round function, especially since a lot of my data uses decimals that end in 5.   I read about banker's rounding (number round either up or down to the nearest even integer, if I understand correctly).  But I can't make sense of what I'm getting when I use the round function - I'm guessing it's rounding to the nearest odd number?   Am I using the ROUND function incorrectly?   Can someone confirm this, and if so, why doesn't tableau say this in their function explanation?

I'm attatching a Tableau file as well with the function.... • ###### 1. Re: Understanding how Round() function handles 5's

Hi David,

I recommend using the number format and reduce the number of decimal places, which will give you the conventional rounding. Let me know if this helps.

Cheers,

Sasha

2 of 2 people found this helpful
• ###### 2. Re: Understanding how Round() function handles 5's

Hi David,

When you use the round function it will round to the nearest decimal place that you specify.

Maybe try aggregating the field inside the calculation :

round(SUM([Value]),1)

Try the above and let me know whether that works.

Thanks,

Mavis

1 of 1 people found this helpful
• ###### 3. Re: Understanding how Round() function handles 5's

Sasha,

Thank you!  I can use this for some of my projects.  It's a bit of a hack, still, and isn't quite as explicit to viewers about what's going on, but it will work -  thanks!

I am wondering the formula and reasoning behind making Rounding the way Tableau does, though.  I wonder why they didn't use traditional Rounding as a calculation?

David • ###### 4. Re: Understanding how Round() function handles 5's

Mavis,

Yes! this is a very helpful response.  Thank you!   Do you have an explanation as to why this works, and what the original ROUND calculation is meant to do?

David

• ###### 5. Re: Understanding how Round() function handles 5's

Thanks for all the advice!  I was wanting to understand the ROUND function because I need to create a calculation with conditional formatting (multiple decimal place formats for one measure).  Mavis provided a way to get most of my scripts to work, but I am curious what is the rational behind the ROUND function, and why Tableau doesn't include the conventional rounding method in their calculations.

David

• ###### 6. Re: Understanding how Round() function handles 5's

Hi David,

It looks like Tableau may use something called Bankers Rounding? -

Thanks,

Mavis