The final blog in this mini series on Tableau table calculations focuses on more advanced capabilities users can execute. The first post discussed a foundational understanding of table calculations, and the second tackled the various types of calculations that exist and their variations. Here, we’re jumping straight into the ways you can take table calcs further in your analytics.

## Compute Using Specific Dimensions

Consider this table with my **[Sales]** by **[Category]** and **[Sub-Category]** again, but it also has some states in the U.S. to split it up a bit further and **Year** across the top. I want to add a table calculation to see the **Total [Sales] by [Category]** and** Year** regardless of both **[Sub-Category] **and** [State]**:

If I add a simple **WINDOW_SUM(SUM([Sales]))** it has no idea what my window is that I want to calculate the total over. By simply dropping this calculation in my view, it is Computed Using **Table (across) **and it is simply summing up my sales for every **[State]**,** [Category]** and **[Sub-Category]** regardless of year.

In this case, we need to tell Tableau how to calculate the **WINDOW_SUM** exactly. I can change the **Compute Using** to **Table (down)**, which would simply give the totals by year. **Table (across then down)** and **Table (down then across) **would give the overall total. **Pane** would limit the calculation to the second dimension from the right on the Columns shelf if selected **across** or the Rows shelf if **down**. **Cell** would only perform the calculation on a single cell level:

Now, in this situation I want to calculate across my pane, going down, and continue across the next pane, going down, with the same **[Category]** label. Across the table in this case means across the years, since my years go across the table as they are on columns. For any other dimension, Tableau will start the calculation again. You can see, although grayed out, the **Specific Dimensions** are set to **‘Year of Order Date’**.

But what if my Compute Using is not so straightforward? What if I want to have **WINDOW_SUM(SUM([Sales]))** for **Year** and **[Sub-Category]** only to compare this to the **[Sales] **by** [State]**,** [Category]**,** [Sub-Category]** and **Year**? The cells I’m trying to sum are not adjacent to each other, so my window for the Table Calculations is different to the available visible ‘windows’, namely **Pane** and **Table**:

In this case, I need to specify the dimensions manually. Since I want my total by **Year** and **[Sub-Category]**, I need Tableau to Compute Using **[Category]** and **[State]**. I often find this a bit confusing because basically I want Tableau to act like these dimensions are not in the view and ignore them. However, if you think of it like this, it seems to make more sense: **Tableau, sum up my sales Across [Category] and [State] for every [Sub-Category] and Year**.

The **Specific Dimensions** option is also handy when one or more dimensions are not on your **Columns** or **Rows** but only on **Detail**. If I need to calculate the average of **SUM([Sales])** by **[Product Name]** within a **[Category]** (without using an LOD), I could do that by dropping **[Product Name]** on **Detail**, so I don’t actually have to show all **Product Names, **and then Compute Using **Specific Dimensions **and tick **[Product Name]**. However, you’d normally end up with lots of repeated values since Tableau will still show the **WINDOW_AVG** for every **[Product Name] **when on **Details** instead of **Columns** or **Rows**, even though the value is the same for every **[Product Name] **within each category.

There are a couple of ways to sort this, with the easiest one creating a simple calculations **FIRST() = 0** and drop that on the Filter shelf. Ensure your Compute Using is **[Product Name], **just as with the **WINDOW_AVG**, since you want to calculate **across [Product Name] for every [Category]**, and this will basically keep the first mark for every category. Since it’s just repeating the same values for every **[Product Name]**, keeping the first one is just fine:

## Sort Order

Finally, there are three other options you can change in some of the table calculations when selecting Specific Dimensions as your Compute Using setting, namely **At the Level**, **Restarting every **and **Sort order**. Let’s start from the bottom upwards with **Sort Order**, as this is the most commonly used option of three.

Let’s assume you want your cumulative Sales by year, and you go for a **RUNNING_SUM** of** SUM([Sales])** to get this. This would normally be fine, and you can select either **Pane (down)** or **Specific Dimension** and select your **Month** dimension. However, what if you want – for whatever reason – to do a reverse cumulative Sales:

This is where the sort order comes in. I’ve selected **Specific Dimensions** and my **Month** dimension to ensure the **RUNNING_SUM** runs across **Month** for every **Year**. Then I changed my **Sort Order** to **Custom**, selected **[Order Date]** and an aggregation of this. You need to select an aggregation, as Tableau needs to aggregate the Sort field to the level of detail of your view prior to sorting. I’ve selected **Minimum**, but **Maximum** would do fine as well – you basically tell Tableau to sort your **Table Calculation** on the **Minimum** (or **Maximum**) date for every mark, and in this case, it would sort it based on the first (minimum) date (usually the 1^{st}) for every month. Count or Count Distinct are also available options, but that would just sort it by the count (distinct) of row-level date values (days) for every mark, meaning February will always be the lowest and in this case wouldn’t make sense.

Now, finally it’s important to sort **Descending** for this specific exercise, as we want to sort from the Latest Minimum date for every year first – which is probably the 1^{st} of December for every year. You can see, the sort order in the view stays the same but is reversed for the **Table Calculation** and starts with the cumulative total for every year in January:

## At the Level

Now, onto **At the Level **and **Restarting every**. You would normally not need to use these options because the impact of this is subtle. However, when we look at the **Specific Dimension** option, you can either select or deselect a Dimension and change the order of the Dimensions. The order basically defines the default sort order, starting from the first Dimension. Having a Dimension selected means that Tableau will use this Dimension to calculate your table calculation **across**, whereas not selecting a dimension means it will form part of the **partition**. So in the prior example, **Year** is the Partition and **Month** is the Across Dimension. In other words, the calculation will reset when a new **year** ‘Partition’ starts, but within the partition the calculation runs across the months.

**At the Level **will basically do the same thing as deselecting the dimension(s) **below** the At the Level dimension. The default is deepest, which is (confusingly) the same as the last dimension in the list – selecting deepest or the last entry doesn’t change a thing, but if you select another dimension, you’re basically ‘deselecting’ the dimension below that one within the Specific Dimensions hierarchy. The subtle difference is whether we are either using exact (dimensional) values or the **position** within the table:

Let’s move onto some Tableau examples. I’ve got a really simple table showing **[State]** and **[Ship Mode]**. I want to add an Index to uniquely reference each state regardless of **[Ship Mode]** So I add a simple **Index()** calc to my table, select **Specific** **Dimensions** and deselect** [Ship Mode],** leaving only **[State]** selected. This doesn’t give me my desired results. Why? Because not every state has data for every **[Ship Mode]**. Because D.C. hasn’t got First Class, First Class in Idaho gets an Index of **3** whilst it’s actually the 4^{th} state in my data. This isn’t what I want. I want every **[State]** to have a unique index, regardless of whatever goes on with the **[Ship Mode]**:

Now let’s change this a little bit. First, I select both dimensions, which basically tells Tableau to Compute the index down the Table. Then I change the **At** **the** **level** to **[State]** and *boom!* It’s doing exactly what I want and basically ignores the **Ship Mode:**

Let’s try a different example. I’ve got my **[Sales]** for the first two months of 2016 by day. I want to do a **LOOKUP** to compare the **[Sales] **by day across the two months – not based on the date but based on the day within the month on which we had sales.

However, there isn’t data for every day; for example, the first two days of January are missing, as well as some other days. So let me use this simple LOOKUP: **LOOKUP(SUM([Sales]),-1)**. First, I calculate this LOOKUP with Year and Month selected and I leave the **At the Level** and **Restarting every **alone. The first two days in February, as well as some random other ones, stay blank as there isn’t an equivalent day within the month in January in my data. Secondly, I reuse this LOOKUP and leave all dimensions ticked. Then I select my Month dimension in At the level’ and *boom*! Instead of looking up the same day, it’s looking at the position instead, regardless of actual day number.

## Restarting Every

Finally, **Restarting Every**. Normally, you’d say your **deselected** dimensions define your partition, window or when your table calculation should restart. For example, if you want a running calculation that only restarts every year, you deselect **Year**. Let’s say you want a **RUNNING_SUM** with a different sort order than my normal order of months; I want a cumulative total in order of month from lowest to highest **SUM([Sales])**. Okay, I set a custom sort order based on **SUM([Sales])** ascending.

Now, because our **year** is defining the partition and therefore **deselected **in my **Specific Dimensions**, the **year** is ignored in the sort order. So the sort order is actually defined by the sum of sales by month without considering **year** if we deselect year. Whereas our view shows the cumulative **SUM([Sales])** by **month** for every **year**, our sort order is actually defined by the (non-cumulative) **SUM([Sales])** by **month **regardless of **year**, so every **month** in our sort order ‘list’ is the total across the years in our view. This doesn’t make sense at all.

In the screenshot below, you can see in the first column that in 2018 our lowest ranking month is January and the second lowest is February then August etc. So you would expect the **RUNNING_SUM** to start with January, and the value for February should be January + February, whereas in actual fact (see column 2), the value for January is already January and February, and for February it’s only February, suggesting the Sort order to start in February, then go to January and then actually April (Jan + Feb + Apr) instead of August. Huh?

If we do a **RANK(WINDOW_SUM([Sales]),’asc’)** to rank the totals each **Month **regardless of **Year**, we can see what is happening (column 3). So the sort order of the **RUNNING_SUM **ignores the **Year. **The **WINDOW_SUM **is Computed Using **Year** and the **RANK** is Computed Using **Month.**

Let’s add a Simple **Rank** that Computes Using** Month **to see what the order should be for every year individually. This is in Column 4. Now, let’s fix our **RUNNING_SUM **by changing our Compute Using to both **Year **and **Month, **and changing the **Restarting Every** to **Year** to ensure **Year **is included in the sort order, since it’s part of my **Specific Dimensions **– see column 5:

The default behaviour in Tableau does make (a little bit of) sense. If you sort normally with nested dimensions, the sorting will be based on the right-most dimension in isolation; otherwise, the order of values would differ across your higher order dimensions. The view below is sorted based on **SUM([Sales])** by **[Sub-Category]** and clearly within the Consumer **[Segment]**, the first **[Sub-Category]** is not the best-selling one, but overall it is. The same logic is applied when using the Custom Sort order with **Table Calculation**:

Can I break this behaviour outside a table calculation as well and basically have a different order of **[Sub-Category]**** **for every segment? Yep. You can by using **Nested sorting,** which ensures a sorting on **[Sub-Category] **within every **[Segment]**:

Keep in mind that if a dimension is deselected in the **Specific Dimension **list, Tableau will not consider it in your **Sort Order** at all. To ensure Tableau does actually consider the dimension in your** Sort Order**, keep it in your **Specific Dimensions **and use the **Restarting Every** to control your partitioning instead.

## Conclusion

That wraps up our mini blog series about table calculations. If you’re interested in more Tableau support, explore our other blog posts or reach out to our team of experts. We’d love to come alongside you and help you make the most of your data!