There was a Tableau forums thread on At the Level awhile back where Matthew Lutton asked for an alternative explanation of this somewhat puzzling table calculation configuration option, and I’d promised I’d take a swing at it. Plus, I’ve been deep into book writing about shaping data for Tableau, and a taking a break to write about obscure table calc options sounds like fun! (Yes, I’m wired differently.)

Read on for a refresher on addressing and partitioning and my current understanding of uses of At the Level for ordinal table calculations such as INDEX() and SIZE(). Part 2 will cover LOOKUP(), and Part 3 will cover WINDOW_SUM(), RUNNING_SUM(), and R scripts. If you’re new to table calcs, read through at least the Beginning set of links in  Want to Learn Table Calculations. Thanks to Alex Kerin, Richard Leeke, Dimitri Blyumin, Joe Mako, and Ross Bunker for their Tableau forum posts that have informed what you’re about to read.

Review of Addressing and Partitioning

When we set the Compute Using for a table calc, we are setting the addressing—Compute Using and addressing are synonyms. The addresses—also called “rows in the partition”—are the distinct combinations of values of the dimension(s) used for addressing. Tableau computes a table calculation result for each address. Here’s a quick example using an INDEX() function in Superstore Sales:

The addressing is the default Compute Using of Table (Down), which for this pill arrangement means the addressing is on Department and Category, so Tableau is computing the result for each combination of Department and Category.

All of the dimensions in the view that are not part of the addressing are automatically part of the partitioning. Tableau will restart the computation of the table calculation for each new distinct combination of values of the dimension(s) (and the occasional discrete measure) used for partitioning. What “restart” means depends on the particular table calculation function in the case of INDEX() that means starting back at 1. So if we set the Compute Using (addressing) of the Index calculation to the Category dimension, then it will partition (restart) on each Department:

One of the nice things that Tableau does is that as dimensions are added to the view, they are automatically added to the partitioning. For example, in Superstore Sales if we have a % of Total Sales Quick Table calc on Sales that is addressing on the Order Date:

And then we add the Region to the view, Tableau will continue to address on the Order Date and partition on the Region:

Now, if I expand out the YEAR(Order Date) to include the QUARTER(Order Date), I see this view where the % of total is being computed along all Quarters for each Region:

What if I want to generate a set of results for each Year & Region?

Oftentimes I find myself thinking about table calculations is that I first consider the partitions—the boundaries—of the table calculation. Since Tableau requires me to specify the table calculation in terms of the addressing, that means I’ve got to do a little bit of mental gymnastics. There are three dimensions in the view – Year, Quarter, and Region, so if I want to partition on the Year and Region, then I need to address on what’s left—the Quarter. However, there’s no easy option on the table calc pill to just set the addressing to the Quarter, the only date option is Order Date:

This is an effect of using Tableau’s built-in date hierarchy. Fortunately, we can still use Tableau’s date hierarchy by using an alternative means to set the addressing. To do that, we have to use the Edit Table Calculation… option, which brings up this dialog:

Then on the “Summarize the values from:” combo box I’ll choose Advanced…, which brings up the Advanced dialog:

This shows all the dimensions in the view, with the dimensions used for partitioning on the left and addressing aka Compute Using on the right.  The order of the dimensions for addressing is important, because that determines how those dimensions are going to be sorted for the table calculation, and also affects At the Level.

Here we can see that Tableau now shows not just Order Date as a single value, but includes both the dimensions of Order Date in the view. Now I can double-click on Quarter of Order Date to make it part of addressing, then click OK a couple of times to close the dialogs, and see that now the % of total is computed along the Quarter for each Region & Year:

Ok, so that’s the super-quick refresher on Addressing and Partitioning, now to begin our exploration of At the Level.

At the Level

Rather than define what At the Level does just yet, it’s easier to see it in action first. One thing that confused me about At the Level was that I’d only need it once in awhile, and then when I used it the behavior always seemed different. The factor that I’ve identified is that At the Level does behave somewhat differently for three categories of table calculations:

• Ordinal calculations such as INDEX(), FIRST(), LAST(), SIZE(), LOOKUP(), and the RANK_ functions.
• WINDOW_, RUNNING_, and the R SCRIPT_ functions all aggregate measures along the addresses of each partition.
• TOTAL() is an aggregation of the inner measure at a different level of detail. At the Level has no effect on TOTAL().

This post covers some of the ordinal calcs, and will be continued in Part 2 for LOOKUP. Part 3 will cover the WINDOW_, RUNNING_, and R scripts.

At the Level for Ordinal Table Calculations

We’ll start the exploration of At the Level with the ordinal table calculations. Here’s that view of Index addressing on Category:

In the Edit Table Calculation dialog, At the level is greyed out:

This is because there’s only one dimension in the view. At the Level is only available when there are two or more addressing dimensions.

If we put both Department and Category into the addressing section of the Advanced Compute Using dialog, we’ll see the same results as Table (Down), and At the Level is now available:

Explaining the At the Level Combo Box

If we click on the At the Level combo box, we see three options: “Deepest” and the two selected addressing dimensions:

What appears in this menu are only the addressing dimensions and the “Deepest” option. “Deepest” sets At the Level to be the bottom-most dimension in the menu, and has no effect on the results of the computation, so if I don’t need At the Level then I leave it at Deepest.

If we specify a dimension for At the Level and then sometime later add other dimensions to the addressing below the chosen At the Level dimension, Tableau will leave At the Level on the chosen dimension. I’ve never used this option myself, though that’s more due to how I put together views: I try to get all the dimensions in the view (on Rows, Columns, Pages, and the Marks Card) first, and add all the dimensions for addressing, and only then set At the Level.

Seeing Results

To review, if we choose Deepest or the bottom-most dimension there won’t be any effect. If we choose a higher-up dimension, like Department in this case, so the addressing for the Index calculation is on Department, Category, At the Level Department, then the table calculation returns different results:

Since the addressing is on all the dimensions in the view there is only one partition for the entire view. What Tableau is doing is only incrementing the Index value for each new Department while still returning a value for every address/row in the partition. I’ll step through this table for the first several rows:

1. Furniture/Bookcases is the first address and gets an Index of 1.
2. Furniture/Chairs & Chairmats is the next address and has the same value of Department, so the Index is not incremented and remains 1.
3. Furniture/Office Furnishings still has the same value of Department, so the Index is still 1.
4. Same for Furniture/Tables.
5. Office/Supplies Applicances has a new value of Department, so the Index is incremented to 2.
6. Office Supplies/Binders and Binder Accessories has the same value of Department, so the Index is not incremented and remains 2.
7. And so on…

This is true for all of the ordinal calculations, here’s a view with several of them:

The SIZE() calc is only incrementing on the Department, and there are 3 Departments, so every address gets a value of 3. The FIRST() calc again is only incrementing on the Department,  so all the values of FIRST() for Furniture have the same 0 result, then all the values for Office Supplies have the same -1 result, and so on.

Here’s my current attempt at a working definition of At the Level for ordinal calcs:

The default setting of “Deepest” or choosing the bottom-most addressing dimension tells Tableau to increment an ordinal table calc on each address, when we set the At the Level dimension to be a higher addressing dimension then we’re separating the incrementing of the ordinal calcs from the addressing.

A key point here is that this only applies to the addressing dimensions. If I drag another dimension into the view, like YEAR(Order Date), that is automatically added to the partitioning so the calculation totally restarts for each new Year:

However, if we add that third dimension to the top of the addressing and leave the At the Level on Department, we get very different results:

Based on these settings, Tableau now increments the Index for each and every new combination of Year and Department, while repeating the values for each Category within those dimensions.

You may ask, when might we want to use At the Level for an ordinal calculation? To explain this, I need to define granularity: The granularity of the view is the distinct combination of values of dimension(s) on Pages, Rows, Columns, and a given Marks Card. The most common use case for At the Level that I’ve seen is when we want the table calculation to increment at a coarser granularity than the granularity of the view. In the example above with Department and Category in the view, we were incrementing the Index on the coarser granularity of Department.

At the Level and Sparse Data #1

At the Level really shows off it’s power when we’re dealing with sparse data. Thanks to  Dimitri Blyumin, here’s a great example. Before Dimitri found this solution, we had to deal with situations like this using a custom calc built with LOOKUP() and PREVIOUS_VALUE(). Here’s a view with Container on Rows, SUM(Sales) on Columns, and Department on Color:

What if I want a unique rank or index for each Container? In other words, I want to see this:

I can bring in an Index, make it discrete, and see this:

 Starting with Large Box, the Index is returning multiple values. Without At the Level, No matter the compute using settings I pick of Table, Department, Furniture, nothing will work. Here’s a view that shows why:

There is no data for Jumbo Box/Office Supplies, so the Index for Office Supplies is skipping that combination and continuing on, so for Large Box and on down there are two values of the Index for each.

The solution to getting the results we want is to use an Advanced Compute Using on Container, Department, At the Level Container:

This now only increments the Index for new values of Container, and when we put that Compute Using into the view, we get the desired results:

In a sense, At the Level lets us “ignore” the Department dimension and only increment the Index on Container. However, Tableau is still computing a result for each Department in each Container. I’ve come to think that a better description is that At the Level gives us control over what addressing dimension(s) we want the ordinal calc to incremement on.

At the Level and Sparse Data #2

Here’s another example drawn from the Tableau forums. In this case the view is showing 10 customers, but 12 marks because there are multiple Customer Segments for some customers. The goal is to be able to say in the Title that there are 10 customers displayed (or however many customers are selected).

Since SIZE() counts the number of addresses in the partition, and Customer Name is a dimension, it would seem to be the perfect calc to count the number of customers.

However, when we put SIZE() into a view, without using At the Level there’s no combination of addressing dimensions that will get us to 10. We’ll get 12, 4, 3, etc.

With a Compute Using of Customer Name, Customer Segment, At the Level Customer Name we can set up SIZE() to only increment the # of addresses for a new Customer Name, resulting in a total of 10:

An Alternative Using Rank

This is a bit of a tangent, I’ve included it here because it serves as an example of how there are often multiple routes to a solution in Tableau and it can be one way to deal with a complication of At the Level. Tableau’s Rank table calculation functions take an aggregate measure as an argument and rank the values of that measure. Often we think of using things like RANK(SUM([Sales])), but the measure doesn’t have to be numeric, it can be any data type. So RANK_DENSE(MIN([Customer Name])) with a Compute Using of Customer Name, Customer Segment will cause Tableau to rank each customer name alphabetically, with ties counting as the same number. This gives us a distinct value for each Customer Name:

The largest rank is the number of Customer Names, so we can take the calc and create a fully optimized calc with PREVIOUS_VALUE(WINDOW_MAX(RANK_DENSE(MIN([Customer Name])))). The WINDOW_MAX gets the largest rank, and the PREVIOUS_VALUE() is an optimization so the calc is only performed once for each partition.

There’s a quirk with At the Level where if the dimension used for At the Level (or any addressing dimensions above it) are on the Level of Detail Shelf while other addressing dimensions are on Rows, Columns, and/or Pages, Tableau triggers domain completion for those addressing dimensions. This is avoided by the Rank calc since it doesn’t need At the Level at all.

Conclusion

The clue for me when I might want to use At the Level for an ordinal calc is when some aspect of the view requires a finer granularity, while I want my ordinal calc to “ignore” or skip over that finer granularity to increment at a coarser granularity. For me, At the Level is kind of like the pipe wrench that sits in the bottom of my toolbox: not needed and unnecessary until it is exactly the right tool for the job.

Part 2 of this series will cover some interesting aspects of using LOOKUP, which adds another wrinkle to how At the Level works. Part 3 will go over WINDOW_, RUNNING_, and R scripts.