Rounding Calculations in Tableau (and Excel and Alteryx)

image

I was doing some table calculations in Tableau and needed complete control over the rounding that was used. Tableau provides a single ROUND function. The exact specification of this (I believe) depends on the data source you are connected too, as it passes through to the underlying server. I needed to be able to look at the effect of the different methods for rounding so had to create some functions reproducing the different rounding methodologies.

Ken Black (@3danim8) has a great post on rounding and all the wonderful quirks of the different way here.

image

There are far too many options for dealing with rounding. Different ways to handle numbers at exactly 0.5: to round up (the one we are all taught), to round down, to round away from 0, to round towards 0, or banker’s rounding (rounding to the nearest even number). There are also some simpler rounding methods (e.g. Round Towards Zero).

This is a set of notes from my playing with rounding in Tableau, Excel and Alteryx. Hopefully useful!

Some Useful Functions and Behavioural Differences

While I am sure there are a myriad of ways to do this, these are the functions I found useful. They are:

    • Round – the default rounding function, round a number to the nearest integer. This is used only for reference.
    • Floor – round a number to the nearest integer lesser or equal than it.
    • Ceiling – round a number to the nearest integer greater or equal than it.
    • Abs – returns the absolute value of x
    • Sign – returns 1 if the number is greater than 0, –1 if less than 0 or 0 if equal to zero.
    • % – the modulo operator (gets the remainder of a division)
    • Int – rounds a number to the nearest integer equal to it or closer to zero.

For some reason (and I have no idea why), the FLOOR and CEILING functions are not available in table calculations within Tableau. You can reproduce the functions using the modulus operator, %.

FLOOR([X]) = [X] – ([X] % 1)

CEILING([X])=[X] + ((1 – ([X] % 1)) % 1)

(The final ‘% 1‘ on the CEILING equivalent function avoids it from moving integers up.)

It is worth noting that Excel has slightly different behaviour for INT. This function in Excel returns the same as the FLOOR function within Tableau. The Excel equivalent to the INT function is TRUNC. Excel doesn’t have a modulo operator but does have the MOD function which performs the same function.

Alteryx has a similar set of functions. The CEILING function is called CEIL in Alteryx. There is not a modulo operator but there is a MOD function. However as this function only works on integers, it is not useful here. Fortunately there are no limitations on the CEIL and FLOOR functions so we can avoid using MOD. Alteryx doesn’t have an INT function, but again we can easily avoid using it. Finally, there isn’t a built in SIGN function in Alteryx but it can be trivially reproduced using the IIF function

SIGN([X])=IIF([X]>0,1,IIF([X]<0,-1,0))

One other thing in Alteryx is that the syntax for the Round function is also different to the others. The other environments take the number of decimal places. Alteryx takes a mult value and rounds the input to the nearest multiple of mult. This works as a great method when not just rounding to nearest integer (e.g. when rounding for round lots of shares in steps of 200).

Rounding Half Up

This is what Tableau does when you use the ROUND function (at least when connected to SQL Server). Easy enough to reproduce not using the ROUND function:

FLOAT(FLOOR([Unrounded]+0.5))

The FLOAT function is optional, but it converts the integer returned by FLOOR back to a decimal number. For reference, the ROUND function, returns a decimal number. This works great but as you can’t use the FLOOR function in a table calculation, it becomes a little more complicated for that case:

([Unrounded] + 0.5)-(([Unrounded] + 0.5) % 1)

Rounding Half Down

Basically symmetric problem to the Rounding Half Up, except you can’t use the ROUND function to help!

FLOAT(CEILING([Unrounded]-0.5))

This works great but again as you can’t use the CEILING function in a table calculation, it becomes a little more complicated:

[Unrounded] – 0.5 + ((1 – (([Unrounded] – 0.5) % 1)) % 1)

Rounding Half Towards Zero

This is the same as rounding the absolute value using the Rounding Half Down method and then applying the sign of the original number:

SIGN([Unrounded])*FLOAT(CEILING(ABS([Unrounded])-0.5))

Again once we have removed the CEILING function becomes a little more complicated:

SIGN([Unrounded])*(ABS([Unrounded])-0.5 + (1 -(ABS([Unrounded])-0.5) % 1) % 1)

Rounding Half Away From Zero

This is the same as rounding the absolute value using the Rounding Half Up method and then applying the sign of the original number:

SIGN([Unrounded])*FLOAT(FLOOR(ABS([Unrounded])+0.5))

Because we are dealing with positive numbers we can replace FLOOR with the INT function for table calculations:

SIGN([Unrounded])*FLOAT(INT(ABS([Unrounded])+0.5))

Again, because we are dealing with positive numbers if using this formula in Excel you can use the INT function and don’t have to switch to TRUNC.

Banker’s Rounding (Rounding Half To Even)

image

This is a fairly horrible process. Again we can deal with the absolute number and then re-apply the sign afterwards. In this case if the integer part is even we are rounding halves down and if it is odd then we are rounding halves up. As we are dealing with the absolutes, we can use INT instead of FLOOR.

First part is to determine odd versus even. The number is even if MOD(INT([Unrounded])) equals 0. Combining this with the formulae above gives:

SIGN([Unrounded])
* IIF(INT([Unrounded]) % 2 = 0, Ceiling(ABS([Unrounded])-0.5), INT(ABS([Unrounded])+0.5))

As a final step removing the CEILING function so it can be used on table calculations:

SIGN([Unrounded])
* IIF(INT([Unrounded]) % 2 = 0, ABS([Unrounded]) – 0.5 + ((1 – ((ABS([Unrounded]) – 0.5) % 1)) % 1),
INT(ABS([Unrounded])+0.5))

I am expecting there is a nicer way to do this but it achieved what I needed.

Other Rounding Methods

Rounding Down (i.e. rounding to the nearest integer equal to or lower than the unrounded value) is given by the FLOOR function:

FLOOR([Unrounded])

or

[Unrounded] – ([Unrounded] % 1)

Rounding Up (i.e. rounding to the nearest integer equal to or larger than the unrounded value) is given by the CEILING function:

CEILING([Unrounded])

or

[Unrounded] + ((1 – ([Unrounded] % 1)) % 1)

Rounding Towards Zero (i.e. rounding to the nearest integer equal to or close to zero) is given by the INT function. Remember the equivalent in Excel to INT is TRUNC, the alternative formula is provided as Alteryx has no INT function.

INT([Unrounded])

or

SIGN([Unrounded]) * FLOOR(ABS([Unrounded]))

Finally Rounding Away From Zero (i.e. rounding to the nearest integer equal to or further away from zero). There is no built in function to do this but it can be easily done by rounding the absolute value up and then reapplying the sign:

SIGN([Unrounded])*CEILING(ABS([Unrounded]))

or avoiding the CEILING function:

SIGN([Unrounded])*(ABS([Unrounded]) + ((1 – (ABS([Unrounded]) % 1)) % 1)

For what it is worth I have published a Tableau workbook to Tableau Public with these formulas. You can get it here

7 thoughts on “Rounding Calculations in Tableau (and Excel and Alteryx)

  1. Reblogged this on 3danim8's Blog and commented:
    I have never reblogged someone else’s work. However, when someone writes a treatise on a topic like this article is, I have to capture the information because it will be needed in the future. This is a Master’s thesis delivered in the form of a blog post.

    Believe me when I tell you that you feel like you are on a merry-go-round when you are studying founding. Your brain is saying, ‘ around and around we go, where we stop, nobody knows”. Even Mr Ned of Alteryx got sucked into the vortex one day when sent him an innocent question! I still feel bad for wasting part of his day.

    So now we have a place to go. This is the definitive guide for founding. I can only imagine how long this took James to write. Thanks so much for sharing this my friend.

    Like

    • Hi James,

      Can you please help me how to get the exact value which is in the data source without rounding off.
      Issue: Tableau data is rounding off the value which is present in the excel.

      Thank you.

      Like

  2. Hello all, i have created a calculation1 field 1.2/0.05 and i get the result as 24 which is correct however i have created calculation field which says calculation1 = 24 and i get the result as false , could you please let me know as to why is this happening it should ideally show as true

    Like

  3. Hi James, I just found this post, it’s very complete!! I’d written a less detailed post back in 2012 at http://drawingwithnumbers.artisart.org/unexpected-results-rounding/ when I was faced with similar problems http://drawingwithnumbers.artisart.org/unexpected-results-rounding/.

    I do have an explanation why FLOOR(), CEILING(), FINDNTH(), REGEX*, and other functions are (still) not available for table calcs in Tableau (at least as of 10.2, I haven’t run tests on newer versions yet), it has to do with Tableau’s order of operations and development team structure. Tableau has 4 different places where computation is done: 1) the data source (SQL Server, Oracle, etc.) for record-level and most aggregate calcs, 2) in a Tableau data extract for record-level and most aggregate calcs (note that this includes Excel & text connections using the default connector since they are technically using a shadow extract), 3) in Tableau for other aggregate calculations that use multiple sources in a data blend as well as some other cases for data blending, and 4) in Tableau for table calculations. So even though 2,3,4 are all computed “in Tableau” those parts of Tableau are worked on by different development teams and features get added in one that aren’t necessarily added by others. In any case I created a feature request for at least some of these on the Tableau forums: https://community.tableau.com/ideas/6239.

    Cheers,

    Jonathan

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s