**Iravati Abhyankar, ****BI Consultant & Trainer, CRG Solutions**

We have plenty of applications in day to day scenarios where Tableau’s Table Calculations need to be used. And the fact that Tableau has Quick Table Calculations that are available just in the context sensitive menu (on the right-click) on Measures makes those very easy and friendly.

At the same time, it’s interesting to note how Tableau allows us to define complex calculations and better control with Table Calculations like WINDOW_SUM() and INDEX() and SIZE() and PREVIOUS_VALUE() etc.

Listed below are some usecases that I find fascinating. The ease of use gets highlighted in these.

**The Usual**

Difference, Percent Difference, Year To Date, Year over Year Growth percentage and even YTD Growth are commonly required and available in the Quick Table calculations if you have the right Time Dimension columns included in the view.

Percent of Total is another must-have to show contribution, comparison and highlight certain extra-ordinary figures or observations.

For Year to Date and YoY to work, we need to have Year as a separate dimension and not a continuous MY Axis. The comparison by default is with previous but practical scenarios expect the monthly figures (amounts, counts,..) to be compared against same month last year rather than previous month. This makes sense because there is seasonality factor. For example, sales around festival months should not be compared against prior months but against festival months of the prior year.

In terms of Partition & Addressing, Month is partition and comparison is ALONG Year (FOR EACH Month).

**Hence it is very important to learn the terms **

- Across / Down
- Table / Pane / Cell
- Previous / Next / First / Last

Compute Using that allows us to select the dimension(s) that define the scope.

Most of the trouble shooting when the amounts are not right need to focus on the above mentioned settings for the table callculation. When you hover over the small triangle that shows up on the pill when table calculation is applied, it shows a brief description of what exactly is being displayed or how it is being arrived at!

**Table Calculations as a filter**

*Rank*

Rank is commonly used instead of Top N when there are multiple dimensions involved. As seen in the example below: Top 2 Subcategories within each Region are expected to be shown. Label shows rank per Pane (within region) and SubCategory Top 2 by Profit is used as a filter. The result is not correct – if we compare the values WITHOUT filter.

Instead we should be using Rank (which is on the label) in the filter as in the image below.

*Lookup Filter to show correct Percent of Total*

You must have come across Lookup filter.

When Customer Segment-wise contribution is shown but user expects a Segment filter as well in the view.

The default way to present being as in Image 1. But the moment user filters one or more of the segments, the Percent of Total is auto-adjusted to give 100% divided among remaining segments.

That is where a Lookup Filter comes in handy. In place of Segment filter, we will define a field that will act as a filter and allow user to filter segments without impacting the percent of total.

**Secondary Calculation for Pareto Analysis**

I espcially like the way Tableau lets us define Secondary Table Calculation. The most common usecase being of Pareto Analysis.

In order to find the 20% (say Customers) that contribute the 80% (of say Profit),

Customers are listed in descending order of Profit. Starting from the most profitable customer, we then computer the Running Total. Here’s where secondary calculation is required to check the percent of total upto that point. That means, after each customer’s profit getting added to the Running Total, we eant to observe at what point does it reach 80% of the total.

**Moving calculations**

Moving average or sum is another of functionality that is made easy to configure and apply by Quick Table Calculation. An interesting application of “wrap around” moving average is mentioned in my old blog

https://www.crgsolutions.co/blogs/moving-calculations-in-tableau-wrapped-around/

**And Finally, Previous_Value() Function**

Although Tableau does not have looping (that involves “for” or “while” kind of coding), it let’s us accomplish certain of those tasks with the help of this function Previous_Value()

Some of the common applications are:

- Imputation – replacing Nulls with earlier value

- Running Sum and even running Product

- Calculating Compound Interest

For compound interest calculation, we need to always consider prior amount on which interest should be applied and not the original principal amount. Principal is kind of moving .. or growing in this case.

**Hence if 8% interest is to be applied every year **

- PREVIOUS_VALUE(1000) * 1.08
- Will return the resulting amount for the current year.
- The value 1000 inside the paranthesis is replacement if earlier value is Null.

Although this is not an exhaustive list of Table calculations, I thought of sharing the ones that I needed to use very frequently over the years. I hope you all learn and try and apply all these calculations.

## Leave a Comments

You must be logged in to post a comment.