Tableau Table Calculations

In the previous chapter, we learned how to create some simple calculations in Tableau. However, sometimes we might want to use the results of those calculations to create new insights. For instance, at this point in the book, we can use the knowledge we have gained thus far to create fields to show the total number of sales by customer and add calculations to show the average sales for a line item, but we can't use the results of the sales by customer to show the average overall sales by customer. This is where table calculations come in, enabling us to use the results of our prior calculations as input for new ones.

The following topics will be covered in this chapter:

• General table calculations and background
• Creating quick table calculations
• Customizing table calculations
• Setting up manual table calculations
• Practical examples

...

Technical requirements

This chapter uses the Global Superstore dataset, which can be found at http://www.tableau.com/sites/default/files/training/global_superstore.zip.

General table calculations and background

A table calculation is a way to take the results of usual calculations, as defined in the previous chapters, and perform additional calculations based on those results (and not on the underlying data).

Let's take an example of a table calculation that looks at the average of SUM(Profit) by Year (Order Date) (we will learn how to create such a calculation later in this chapter):

In this instance, the average is calculated by looking at the aggregated marks (in this case, ($248,941+$307,415+$406,935+$504,166)/4 = $366,864), not by averaging the profits in the underlying data.

There are two main components to a table calculation in Tableau. The first element is the calculation that we want to perform on those marks (in this case, a straight average on the marks). The second is the scope (or partition), which we will look at in the...

Creating quick table calculations

The easiest way to create a table calculation is to start with the Quick Table Calculations options.

Every pill representing a measure (including a count or distinct counts of dimensions), whether discrete or continuous, holds the Quick Table Calculation option in its own menu:

Using one of the options presented here will replace the current aggregation with a table calculation, and the pill will then include a Δ sign to signify that the calculation is now a table calculation. Let's go through the different options available.

Different options for quick calculations

There are 11 options in total to create quick table calculations:

• Running Total progressively sums the aggregated...

Customizing table calculations

One of the first things that users want to do after creating a quick table calculation is to update the scope of the calculation, as the default choice was not necessarily what they had in mind.

Adapting a % Difference Calculation

Let's plot Profit by Year(Order Date) in rows and Quarter(Order Date) in columns, create a quick calculation for % Difference, and add Profit back into the view by double-clicking on the field in the Data pane (as the table calculation will take over the original Profit field, and it is not possible to add the same calculation twice):

Tableau's default choice was to compute this table calculation along Table (across) and relative to the Previous value, which...

Setting up manual table calculations

While it is possible to create some table calculations easily with the quick options, there are times when an off-the-shelf calculation cannot fit your needs. In this case, you need to create a manual table calculation. This is done by creating a calculated field.

As a reminder, you can create a calculated field by any one of the following three methods:

• Go to Analysis | Create Calculated Field... in the menu bar.
• Right-click any field in the Data pane and use the Create | Calculated Field... option.
• Click the arrow next to the search icon in the Data pane, close to Dimensions, and use Create Calculated Field....

There, you can define your function manually and choose Default Table Calculation options once the calculation is valid. Let's go over the three main types of functions that are available to users.

...

Practical examples

Before concluding this chapter, let's go over two quick examples that are based on questions that were used in real exams.

Moving average

Using Global Superstore, as of June 2012, what is the sum of the sales of the previous twelve months and the following twelve months, excluding the current month?

To do this, we start by plotting SUM(Sales) by Month(Order Date). Then we create a quick table calculation for Moving Average by right-clicking the pill.

To add Month(Order Date) as shown in the following screenshot, you can drop Order Date in rows, then click on the pill menu for the second Month option (the one that reads a month and year) and change the pill to Discrete (in the pill menu).

The default...

Summary

In this chapter, we covered table calculations, which enable us to use the results of our prior calculations as input for new ones. We learned how to create simple calculations with the click of a button, how to change their scope and customize them, and how to use them to create new and more complex calculations. After learning about simple aggregations in the previous chapter, this chapter serves as a good introduction to Tableau's most daunting element when coming from Excel or other BI tools: level of detail calculations. These will be covered in the next chapter, where we will learn how to make use of information that is not visible in the current views.

Questions

Answer the following questions to test your knowledge of the information in this chapter.

Q: Is it true that table calculations take into account the underlying data and can help create weighted averages?

A: No, table calculations use the results of prior aggregations as input for new ones, so a table calculation for an average will do a straight average of the results within the scope, not a weighted average. For an example, please refer to the first section of this chapter.

Q: One of the options for quick table calculations is Total Sum of all values. True or False?

A: False. The different options are listed in the chapter, although you could use one of the manual Window functions to achieve this result.

Q: Table calculations can also be created on dimensions. True or False?

A: True. You can create table calculations on counts and distinct counts of dimensions.

...

Further reading

You can check out the following links for more information about the topics that were
covered in this chapter:

• Different types of addressing: https://help.tableau.com/current/pro/desktop/en-us/calculations_tablecalculations.htm#table-across
• Using specific dimensions: https://help.tableau.com/current/pro/desktop/en-us/calculations_tablecalculations.htm#specific-dimensions
• Top 10 Table Calculations: https://www.tableau.com/about/blog/2019/11/top-10-tableau-table-calculations
• Table Calculations White Paper: https://www.tableau.com/sites/default/files/pages/table_calcs_in_tableau_6.pdf