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.
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:
- Furniture/Bookcases is the first address and gets an Index of 1.
- Furniture/Chairs & Chairmats is the next address and has the same value of Department, so the Index is not incremented and remains 1.
- Furniture/Office Furnishings still has the same value of Department, so the Index is still 1.
- Same for Furniture/Tables.
- Office/Supplies Applicances has a new value of Department, so the Index is incremented to 2.
- Office Supplies/Binders and Binder Accessories has the same value of Department, so the Index is not incremented and remains 2.
- 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:Each distinct combination of values of the addressing dimension used for At the Level and all addressing dimension(s) above the At the Level dimension increment ordinal table calculations. Addressing dimension(s) below the dimension used for At the Level do not increment ordinal table calculations, and instead return the result of the prior address aka row in the partition for each of those addresses.
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.
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.
Excellent coverage, Jonathan. Thanks!
Thank you! This is so informative. I was looking something similar in Tableau as against the Level metric in MicroStrategy.
A really great thorough and easy to follow post! Thanks so much Jonathan!
Thank you very much for this (and your many other contributions)! I continue to wrestle with Tab calcs.
Pingback: Tableau’s TC14 Conference – #DATA14 | mluttonbi
I am finally getting around to reviewing this in its entirety a second time, and I think it has begun to click in the context of Ordinal Calcs. Thank you, Jonathan, for taking the time to explore this in more depth!
I’m glad it was useful to you!
Pingback: Tableau Request Live – The 2nd Episode: Data Densification | mluttonbi
Pingback: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers
I look forward to part 2 and 3
Now I’m also reading this for a second time. Two important little details have jumped out at me this time:
– PREVIOUS_VALUE() is an optimization, so the calc is only performed once for each partition.
– unwanted domain completion is likely what’s been completely confusing me when my SIZE() calcs aren’t working as expected, At The Level & all.
Hi Jonathan, a very insightful article and thanks a lot for your efforts to writing it. I found the examples really useful to think through how to really apply this. I came to this article after watching Joe Mako’s video on data densification that was on Matthew Lutton’s blog. Reading this has made the concepts that Joe explained even more clear in my mind.
A few questions/comments:
1. Any idea of when you are planning to write the part 2 and 3 as I would like to understand the effect of At the level on other calculations?
2. You said “The order of the dimensions for addressing is important, because that determines how those dimensions are going to be sorted for the table calculation”
Would you mind giving an example of where this matter even when the sorting is set to automatic. I was wondering about the order of the addressing for a while when the sorting is not really to set any specific measure and your post triggered my curiosity even more.
3. You said “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.”
Thanks for sharing this insight on how you really put together views using advanced table calculations so that we don’t land in trouble. You remind me of the advice that I have gotten from the first computer science teacher way back in my engineering school days which I have found to be so helpful to me throughout my days as a programmer and now as well in the world of BI “The faster you go to the compiler, the later you finish the problem”. He was recommending us to really think things through on paper and get everything resolved on paper before turning to trying things out to get a solution. You seem to be following a similar methodology which I intend to copy from you and make it my habit so that I build good habits when I work with advanced table calcs in Tableau
4. You said “The granularity of the view is the distinct combination of values of dimension(s) on Pages, Rows, Columns, and a given Marks Card.”
Why would you consider Pages to determine the granularity? I am asking this to understand the Pages shelf better. The way I have understood Pages shelf so far is that it is like a Filter shelf as it helps us to take one value at a time and create a page (like applying one value in a Filter at a time). My thought was that Filters will not have any effect on the table calc (as it really goes on the Where clause in SQL). So should be the case with Pages, I thought – can you please let me know where I went wrong in connecting the dots?
Thanks for your questions! Here are some answers:
1) Somewhere between a few and several months, I’m focused on book writing at the moment.
2) Using Superstore, create a view with Department & Category on Rows (in that order), and an INDEX() table calculation on the Text Shelf with an Advanced addressing on Department then Category, using Automatic sort. You’ll see the Index go from 1-17 starting with the categories in Furniture, then Office Supplies, and so on. Now go back to the Index calc and change the addressing to Category, Department. The Index will change results and be based on the alphanumeric sort of Category. (If you swap the Department and Category pills on Rows so Category is the left-most pill that will be visible).
3) I’m glad this makes sense to you! I learned that particular technique from Joe, of the many things I’ve learned from him it’s one of the most valuable.
4) Pages *looks* like a filter in terms of the visual effect on the view, however under the hood Tableau isn’t issuing any additional queries to the data source, it’s only adding whatever pill(s) are on the Pages shelf into the query/ies it would normally make. So if you have a dimension on the Pages Shelf, that’s added to the GROUP BY of the SQL, if you have a regular aggregate measure then it’s computed as part of an aggregate query, etc. Another point is that, dimensions on the Pages Shelf can be triggers for densification just like Rows or Columns. The visual effect of the Pages Shelf is processed quite late in Tableau’s pipeline, after filters are applied and other calculations are completed (including table calculation filters), so it’s really acting more like a Hide than a filter.
One way to get a sense of this in action using Superstore is to put Customer Name on Rows and Sales on Columns, then also put Customer Name on the Pages Shelf. You’ll see that Tableau is still rendering all the headers of the ~3000 customers, even though it changes which particular Sales bar it’s showing. There’s a single query issued at the very beginning that is effectively SELECT [Customer Name], SUM([Sales]) FROM Superstore, everything else is all happening in memory, and that’s what enables Tableau to also do the animation on the Pages Shelf.
Pingback: Tableau Tips,Tricks,Best Practices - Calculation - Jenny (Xiao) Zhang
Pingback: At the Level – Unlocking the Mystery Part 2: Rank Functions | Drawing with Numbers
Thank you very much Jonathan for this post. I finally understand “At the level”. You are a great teacher.
Hi Jonathan, The way you write and explain any article is amazing. I notice that you write a last of calculations using “First” and “Last”. Can you explain or write an article on what exactly would “First = 0” and “First == 0” would do?
I’d be glad to add that to my list of future topics! The short answer is that there are three major levels of detail that we work with in Tableau: The grain of the data, the viz Level of Detail based on the dimensions in the view, and finally what I call the “display level of detail” or displayLOD that is the marks that we want to see. There are situations where in order to get the calculations right the vizLOD needs to be at a finer grain than what we need for the displayLOD. This means that we need to deal with that finer grain in our calculations and filters. FIRST(), LAST(), and INDEX() are table calculations which work at different levels of detail and can identify the first, last, or Nth marks in a given partition, and because they are table calculations we can add them to the Filters shelf to filter out that finer grain without affecting the results of calculations.
Thanks Jon for the reply.