Adjustment of Number Format Appears to Round Numbers Incorrectly

Issue

When adjusting the number of decimal places shown for a number, numbers might appear to round up or down incorrectly. For example, a number displayed as 40.5 might change to 40 when the formatting is changed to show zero decimal places.

Additionally, when you use ROUND() within a STR() function, the rounding might be incorrect when connected to an extract. For example: STR(ROUND(SUM[Sales]),2)

Environment

Tableau Desktop

Resolution

Option 1

Rounding may be accomplished in Tableau Desktop with the ROUND() function. However, the aggregation must be considered when writing the calculation as Tableau may interpret the calculation relative to the aggregations applied. For example, if the field needed to round is SUM([Field]) and the calculation implored is ROUND([Field]) then Tableau will interpret said calculation as SUM(Round([Field]).

To fix this behavior, rewrite the above calculation example as follows: ROUND(SUM(Field))

To view a concrete example, please see the attached workbook called Round String.twbx.

Option 2

Use additional string modifiers such as LEFT() as well as additional rounding tools such as CEILING() to create a more accurate representation.

Please note that such modifications can create a long and not very performant calculated field: STR(ROUND(SUM[Sales]),2) = 319.147230000001
Using the above modifications: LEFT(STR((CEILING(SUM([Sales])*100))/100),6) = 319.15

Option 3

View the underlying data to verify the original number to ensure that it is being rounded correctly for the number format. For example, an original value of 40.48 would be correctly rounded to 40.5 if one decimal place were specified and 40 if zero decimal places were specified.

Cause

Tableau Desktop (Tableau Prep) rounds numbers based on the original value in the database, following the "round up by half" convention.

Additional Information

As this behavior is not specific to Tableau Desktop, it is more fundamental to how computers store data, specifically what we consider "decimal" values. Integers are easy, by just adding or subtracting one whole number, you can do the same with binary data:

In decimal: 4 + 1 = 5
In binary: 100 + 1 = 101

However, with decimal values, translating those distinct values to a binary representation, sometimes a simple representation in decimal does not translate perfectly to binary.

For example, .1 is simple in decimal but the number representing this value in binary might simultaneously represent both .0999999999999999 and .1000000000000001 and all numbers between. (Notice there are exactly 16 decimal places in each number.) This can be handled when we show the number values in the display layer, but doing math on the values or converting the values to strings will display the underlying values and this behavior changes based on a number of factors including the data base.

Here are a few examples / articles that demonstrate how this issue manifests across languages and is a fundamental issue in computer science.

For discussion on this topic and possible workarounds in Tableau Desktop, please see the following Community Forums thread: Round and then convert to a string.

For more information about the ROUND() function, see the following Tableau Help documentation: Number Functions.