Video |

Tableau | Data visualisation | Analytics

Ceiling and Floor functions in Tableau

CEILING rounds up, FLOOR rounds down — think of reaching the top or bottom of a room.

Tim Ngwena •11 May 2021 •1 min read •5 min watch •2.9K

Part ofTableau Functions

Watch on YouTube

CEILING rounds a value up to the nearest whole number while FLOOR rounds it down — think of reaching the ceiling of a room versus the floor.
Set up a test table with decimals by using an average measure (e.g. average sales) and increasing the displayed decimal places via field formatting.
You can write quick ad hoc CEILING/FLOOR calculations directly in the calculation window to compare results side by side without creating saved fields.
Watch the editor quirk where typing a function immediately next to an existing one deletes the previous function — add a space first.
Tableau's decimal-place formatting already rounds automatically (up above halfway, down below), so 215.9 displays as 216 at one decimal place.

0:00Hey it's Timmy, in today's video we're

0:01going to be looking at the ceiling and the

0:03floor function

0:04in Tableau. Essentially these round up and

0:06round down in Tableau, so let's get stuck

0:08in,

0:08let me show you how they work. If I hit the

0:11superstore sales data source here,

0:13it's just to save data source, so whatever

0:15data source you've got access to, all we

0:17need to do is

0:17make sure we create a table which has some

0:20decimal places in it. So let's go ahead and

0:22bring sales in

0:23to get some decimal places, I'm actually

0:25going to make this an average and I'm going

0:27to put this

0:28back on label so we get an actual table and

0:30then we're going to break this down maybe

0:32using

0:33subcategories. So if I go get my product

0:35hierarchy here, drag subcategory down, you

0:37'll see that we

0:38get a bunch of values. Tableau does sort of

0:40look at the format and it decides to remove

0:42the decimal

0:43places, so I'll right click on this, go to

0:45format, then go to field, then select

0:48average sales and

0:49what I'll do is I'll do number custom and I

0:51'll bump it right up so we've got lots of

0:54decimal

0:54places available to us. What I will also do

0:57is I'll just go to the formatting option,

0:59so let me just go to the font here and what

1:02I will do is I'll make the font a little

1:04bit larger

1:05so that you can see that more clearly. So

1:07here we can see everything's working

1:09perfectly fine

1:10and because we're taking averages it ends

1:12up sort of resulting in some decimal places

1:14.

1:14So let's take a look at the first one which

1:16is the ceiling function. The best way to

1:18think of this is

1:19a room. To reach the ceiling you have to go

1:21up and to reach the floor you have to go

1:23down, so that's

1:24exactly what the functions do. Ceiling

1:27rounds up and floor rounds down essentially

1:30. So let's go

1:31ahead and let's just edit this field in

1:33here inside of the calculation window. So

1:36all I'm

1:37going to do, I'm going to hit a space,

1:39there's a little bit of a weird behavior

1:40where if you just

1:41start typing the function right next to a

1:43previous function it sort of deletes the

1:44function that you

1:45were just sort of following. So let's just

1:48type in floor here, you can see that it

1:50auto completes

1:51and it gives us two brackets. So what we're

1:53going to do, we're going to grab this and

1:55we're going to

1:55put this here and what I will do is I will

1:57bring the average sales back in as well so

1:59we can see

2:00this. So what I'll do is I'll actually copy

2:02this in advance and now that we've written

2:04that floor

2:04around the average sales hit enter you'll

2:07see that you get these values. Now to sort

2:09of see what that

2:10actually looks like versus the original set

2:12of values let's just hit paste on average

2:15sales,

2:15you'll see that it goes there but it doesn

2:17't come into our table. I can now drag it

2:19into our table

2:19and we get the decimal places back. So let

2:21's have a look and see what this is doing.

2:23So essentially

2:24this should be rounding down under every

2:27single circumstance okay so 215.9 should go

2:30down to 215.

2:32Essentially it's going to the nearest whole

2:34number yeah so 230.7 is going down, 34 it's

2:37going down,

2:38133 is going down. So essentially these

2:41numbers on the right should always equal

2:43what's here on

2:44the left essentially. There shouldn't be

2:46anything that's sort of going up or down

2:47and that's exactly

2:48what's happened. Now what I'm going to do

2:50is I'm just going to double click inside of

2:51this floor

2:52function that we've written here. I'm going

2:54to copy it again and I'm going to double

2:55click below

2:56to get to the new function. Then I'm going

2:58to paste this and instead of floor I'm just

2:59going

2:59to type ceiling okay so ceiling. You'll see

3:02that it comes up there for auto completion

3:04and we just hit enter now that it's written

3:07in there hit enter and now you can see that

3:09on the

3:10ceiling function it's doing the opposite

3:12thing. And so you can sort of see which one

3:14is which if

3:14you just look at the top here you can see

3:16that the functions are sort of described

3:17there. That's

3:18sort of why I wrote them in this little

3:20calculation window on the left hand side

3:22because it doesn't create the calculations

3:25it just sort of does the quick ad hoc

3:27calculations and then

3:28it names the columns accordingly. So you

3:30can actually see the columns here inside of

3:32this table

3:33at the top so you can see which one's doing

3:35what okay. So that's pretty much it there's

3:37not much

3:37more to these functions they're just really

3:39handy for rounding up and rounding down.

3:41Actually tableau

3:43if you tell it how many decimal places you

3:46want will sort of make the best job of what

3:49it can do.

3:49So in this particular case I'm actually

3:52looking at the wrong thing here if I go

3:54back and select

3:55just two decimal places you'll see that it

3:58looks like this. If I go down to one

4:00decimal place

4:00you'll see that it actually rounds 215.9 up

4:05because essentially the seven rounded up

4:08makes this round up to 216. And so that's

4:11that's basically how it works when you

4:13specify decimal

4:14places tableau is doing this automatically

4:17if it's over halfway or round up if it's

4:19under halfway it

4:20rounds down and so on and so forth

4:21depending on the number of decimal places

4:23that you're sort of

4:23going for it'll sort of try and make the

4:25best sense of what's going on okay. So that

4:28's pretty

4:28much how it works in a nutshell and

4:30hopefully you found this video useful if

4:33not please let me know

4:34what you'd like to see instead and check

4:35out some of the other videos on logical

4:37functions that I've

4:38got across my whole channel also visit

4:40tableautim.com to watch lots of other great

4:43content that I've

4:44got available it's also on youtube but I

4:46think it's better to browse it there and

4:47also join me

4:48on discord if you've got any questions I'm

4:50always there sort of answering questions

4:51from time to

4:52time so yeah check it out and I'll catch

4:53you in the next video.

Rollup

CEILING and FLOOR let you force a number up or down to the nearest whole number in Tableau, which is different from how Tableau's own decimal-place formatting rounds values automatically.

Tim builds a quick test table using average sales by subcategory in Superstore, so the values contain decimals to round.

The Breakdown

Build a table with decimal values 0:09

Use an average measure (rather than a sum) broken down by a dimension so the results naturally include decimal places. Increase the field's displayed decimal places via format > field so you can actually see the decimals to test against.
The ceiling/floor concept 1:15

Think of a room: reaching the ceiling means going up, reaching the floor means going down. CEILING always rounds a value up to the next whole number; FLOOR always rounds it down, regardless of how close the decimal is to the halfway point.
Write ad hoc calculations to compare 1:31

You can type FLOOR() and CEILING() directly into the calculation editor as quick, unsaved ad hoc calculations rather than creating permanent fields — useful just for testing or comparing behaviour side by side. Pull the original average measure in alongside them so you can visually check each function against the source values.
Apply CEILING and confirm it always rounds up 2:50

Copy the same calculation, swap FLOOR for CEILING, and confirm every result now rounds up instead — the ad hoc calculation names each column after its function so you can tell at a glance which is which.
Compare against Tableau's automatic decimal rounding 3:41

Tableau's built-in decimal-place formatting rounds automatically based on standard rounding rules — up if the value is over halfway, down if under — which is different behaviour from CEILING (always up) and FLOOR (always down). Changing the number of displayed decimal places changes what gets rounded and in which direction, so don't mistake formatted display rounding for CEILING/FLOOR logic.

Worth Knowing

Typing a function directly next to an existing one in the calculation editor can delete the previous function — always leave a space first.
Ad hoc calculations written in the calculation window don't create saved fields; they're just for quick testing and get labelled by function name in the resulting columns.
Tableau's automatic decimal-place formatting rounds using normal halfway rules (e.g. 215.9 displays as 216 at one decimal place), which is not the same as CEILING or FLOOR behaviour.

Use It When

Reach for CEILING or FLOOR when you need a value to always round in one direction regardless of how close it is to the next whole number — standard decimal formatting won't guarantee that.

How this Rollup was made provenance & method

A Rollup is drafted by AI from the video's transcript, then reviewed and edited by Tim. Everything used to produce this one is listed below — the model, the exact prompt, and the source video — so the process is transparent and reproducible.

Transcription: On-device — NVIDIA Parakeet v3 for recent videos, OpenAI Whisper large-v3 for earlier ones. The transcript never leaves the machine or gets published.
Drafting: Claude Sonnet 5 in the cloud, from that transcript.
Prompt: The exact Rollup prompt (v2) — the full system prompt, unedited.
Source video: Watch on YouTube
Drafted: 5 July 2026 at 09:38
Reviewed & edited: 5 July 2026 at 09:41 · by Tim Ngwena

Model + prompt + video is everything you'd need to recreate a Rollup like this yourself. The one thing we don't share is the transcript.

Rights. The video and its transcript are the property of TN Media Ltd. Unauthorised use or download is prohibited. © TN Media Ltd.