In my previous blog post, I started with some basics of table calculations in Tableau. This post and the next are going to build on that foundation by walking through the various kinds of table calcs there are and finally discussing more advanced options for this functionality.

For each of the types of table calculations, I’ll explain what each does and apply it to an example use case, so you can see it in action. Without further ado, let’s jump in.

## RUNNING Calculations

The RUNNING calculations cumulatively aggregate marks across a defined window; for example, **RUNNING_SUM(SUM([Sales]))** will give you the cumulative sum of sales. The value in your last mark or cell in your window should equal the total value for that window. There are other RUNNING calculations such as **RUNNING_AVG** or **RUNNING_MIN/MAX.**

In the example, you can see two RUNNING calculations, **RUNNING_SUM(SUM([Sales]))** across Sub-Categories for every Category (Pane). This calculation shows cumulative sales but restarts every Category. The second calculation runs across the whole table going down and shows the **RUNNING_MIN(SUM([Sales]))**, which effectively means that it shows the lowest Sum of Sales for the current cell and the previous ones combined, until it hits a lower value.

**RUNNING_SUM **can be used to make a nice visual combining periodic sales and total sales into one, as seen in this bar and line chart combination:

## WINDOW Calculations

WINDOW calculations aggregate the data to a higher level than what’s in your view. Examples are **WINDOW_SUM **and **WINDOW_AVG**. By default, they aggregate the whole partition (window) you specify, but you can also have a moving window within the partition to create moving calculations. In this example, I added a **WINDOW_SUM(SUM([Sales]))** Computed Using **Pane (down)** to show the Category Totals:

I also want to know the **[Sales]** by **[Sub-Category]** as % of the Category Totals. I can simply reuse my table calculation by calculating **SUM([Sales])/WINDOW_SUM(SUM([Sales]))** and ensure this is also Computed Using **Pane (down).**

Next up is a question from my warehouse. They want to know how long it takes to ship orders on average, and they want to track this by day. First, I create a basic calculation to measure the days between a customer placing an order and the order being shipped: **DATEDIFF(‘day’,[Order Date],[Ship Date]). **I then create a line chart with the day of shipping on the Column shelf and my new calculation on the Row shelf, aggregating this to average as it doesn’t make sense to sum up the days between ordering and shipping. This gives me a wild line chart, and I am not much closer to knowing how long preparing an order takes on average.

This is where the **WINDOW_AVG** with a moving window comes in. I add my new field, called **[Days to Ship]**, to a new calculated field **WINDOW_AVG(AVG([Days to Ship]),-6,0)**, where the -6 and 0 defines that I want to look back six (6) data points and include the current one to calculate my average, so I have a moving window of a week. The Compute Using is set to **Table (across)**. In the example below, I change the -6 to a parameter instead, so I can dynamically update my window:

## LOOKUP

The LOOKUP calculation enables you to take the value from a different cell and put it in the current cell. **LOOKUP(SUM([Sales]),-1) **essentially pushes the sales value down one cell, and this enables you to compare the current value to the previous value, for example.

In the example below, I first run a simple lookup to the previous cell across sub-category. This will leave the first cell empty for every category as there is nothing to LOOKUP and pushes the first value to the second cell. The second calculation compares the previous cell value to the current one to calculate the difference across the whole table.

LOOKUPs are useful for comparing values across the same measures. For example, they are used in the **Year On Year Growth **table calculations.

## RANK

**RANK**gives all values their respective competition style and rank, with duplicate values given the same higher rank (or lower rank value).**RANK_MODIFIED**works the same as**RANK**apart from the fact that duplicate values get the same, but lower, rank.**RANK_DENSE**works like**RANK**, but the missing**RANK**due to the duplicate value is not skipped. This means that if there are duplicate values, your highest rank is lower than the count of values being ranked.**RANK_UNIQUE**does not give duplicate values the same rank but ranks these based on the sort order, so the second appearance of the duplicate value gets the lower rank. All rank values are unique.

In this example, **[Sub-Category]** is ranked by **[Sales] **for every year. You can use a Quick Table Calculation to do that, however, the calculation would be **RANK(SUM([Sales]))**. To make it ascending, starting from the lowest **[Sales] [Sub-Category]**, the calculation would be **RANK(SUM([Sales]),’asc’)**:

## FIRST, LAST, INDEX and SIZE Calculations

Then there is the **FIRST**, **LAST**, **INDEX** and **SIZE** calculations, which you could call cell reference calculations. **FIRST **returns the offset from the first cell in your partitions, **LAST** returns the offset from the last cell in your partition, **INDEX** returns the index value of the cell within the partition starting at 1, and **SIZE** returns the size (count of cells) in your window.

In the example below, the **FIRST** and **SIZE** calculations are across **[Sub-Category]**, whilst the **LAST** and **INDEX** are across the whole table. **FIRST** and **LAST** can be useful in LOOKUP functions to reference the first or last value in your partition (think calculating growth). **INDEX** can be useful to filter to the top N records, where the standard filter does not exactly provide what you need. Since **INDEX** is a table calculation, it is applied after all the other filters, so there is no need to apply all other filters to context when using it to create a custom top N filter. **SIZE** can be useful for more complex calculations and logic; for example, when you want to only show a particular value if your window has multiple cells:

In this example, I’m using both an INDEX and a FIRST table calculation. I want to highlight the top 5 **[Sub-Category]** based on **[Sales]**, as well as show **[Sales] **as a % of **[Sales] **of my top-selling **[Sub-Category]**. Finally, I want to be able to filter to a particular **[State] **without having to add this filter to the context since I might want to add an LOD, as well to show total **[Sales]** by **[Sub-Category]** regardless of the **[State] **filter:

My top 5 highlighter is calculated as follows: **IIF(INDEX() <= 5 , ‘Top 5’ , ‘Not top 5’)**. I could calculate this as a Boolean as well and change the true/false aliases. The calculation is Computed Using **Table (down)**. Next, I want to show the % of **[Sales]** compared to my top **[Sub-Category]**. The calculation is **SUM([Sales])/LOOKUP(SUM([Sales]),FIRST())**. I need to ensure that my table is sorted Descending since that will ensure that the **FIRST **offset in my LOOKUP will always be relative to the highest-selling **[Sub-Category]**. You can also manually ensure that it will always LOOKUP against the highest value by using advanced table calculation options and have a custom sort order apply to the table calculation without affecting the view. More about this Specific Dimension and additional options later.

The next example is where we will use **SIZE**. Now, I would like a simple view showing States where we have less than five customers, and I want to show all these customers in a list. There are multiple ways to achieve this – for example, with WINDOW calculations – but we will use **SIZE**.

I simply create a calculation with **SIZE()**, and that’s it. When I put this on the Marks card and change Compute Using to **Pane (down)**, it shows the number of records (in this case, unique Customer Names) within each pane. I move this field onto the filter shelf and filter is to **At Most 5**:

Finally, I’d like to have a bar chart showing my Average Order Value by Month and Year. **[Sales] **in its unaggregated form is a line value, so to calculate Average Order Value, I first need to aggregate the Sales to Order level and then Average that across another dimension: Month/Year of Order Date, in this case.

My calculation is going to be **WINDOW_AVG(SUM([Sales])) **but to ensure the Value is calculated on **[Order ID]** level first, I need to have this in view (if I don’t want to use an LOD). But I don’t want to show all orders in my view. One way to solve this is to only show one order for every month since the final value of my table calculation will be the same for all orders in the same month. A simple **Last() = 0** on the filter shelf will sort this.

Both table calculations use **Specific Dimensions **for the **Compute Using**. This is because **[Order ID]** is on the Marks card and not on **Rows **or **Columns **and therefore not part of the **Table **or** Pane**:

## PREVIOUS_VALUE

Finally, we have **PREVIOUS_VALUE**, which is a weird one. At first glance, the PREVIOUS_VALUE table calculations looks very similar to **LOOKUP([Value],-1)**, but the big difference is that PREVIOUS_VALUE doesn’t apply to a measure you specify, like in LOOKUP, but it applies to itself.

A simple example would be **SUM([Sales]) + PREVIOUS_VALUE(0)**. This calculation would result in a running sum, as what it does is add the previous value of this calculation to the **SUM([Sales])**. The zero (0) argument to **PREVIOUS_VALUE** means that if there is no previous value, assume zero (0), but you can specify the starting value outside of your window:

Now, I hear you thinking, “But we have **RUNNING_SUM** for that.” Well, yes, but there are other things we can do with **PREVIOUS_VALUE** such as (crazy) string calculations like **PREVIOUS_VALUE(“”) + MAX([Sub-Category])**, the results of which you can see below:

And you can also create compound interest calculations like this where we have a monthly interest rate, and the interest is compounded annually. The formula would be something like **PREVIOUS_VALUE(ATTR([Initial Value]))*AVG((1+[Effective Monthly Interest])^12)**.

In the situation where you have no initial value and you deposit a small amount monthly (at the start of the month), and you compound interest monthly too, the calculation would be something like **PREVIOUS_VALUE(0) +SUM([Deposit])) * (1+[Effective Monthly Interest (compounding)])**.

The **[Effective Monthly Interest (compounding)] **calculation, to account for compounding monthly instead of annually, would be **SUM((1+[Annual Interest Rate])^(1/12)-1)**.

## Next up, Advanced Options

The final installment in this series is all about the more advanced capabilities of table calculations, so be sure you check it out next!