Author avatar

Deepika Singh

Create Quick Table Calculations in Tableau

Deepika Singh

  • May 21, 2020
  • 8 Min read
  • 68 Views
  • May 21, 2020
  • 8 Min read
  • 68 Views
Data
Data Analytics
Business Intelligence
Tableau

Introduction

In business intelligence and analytics, it is often necessary to perform quick table calculations. These calculations allow us to quickly apply a common table calculation into the visualization work. Tableau provides a set of pre-computed table calculations that can be used without writing any code. In this guide, you will learn how to create quick table calculations in Tableau.

Data

Quick table calculations are applied to continuous measures and can not be applied on dimensions unless they are converted to the continuous measure type. However, it computes the continuous measure with respect to the addressed dimensions that are part of the view.

This guide will use the Sample Superstore data source available in the Tableau repository.

Creating Calculations

To perform quick table calculation, consider the Order Date variable, with year and quarter as the partition fields, against the Segment variable. The measure you will use is Sales. The scenario involves looking at the quarterly sales of each year across different segments, and then implementing percent of total as a quick table calculation.

To begin, drag Order Date to the Rows shelf and Segment to the Columns shelf. Next, drag the variable Sales to the Text pane in the Marks card. This will generate the following output.

pic1

Drill down the order date to quarters by clicking on the + icon on YEAR(Order Date. This will produce the output shown below.

pic2

The next step is to right click on the Sales variable and select the Quick Table Calculation option, followed by Percent of Total.

pic3

Completing the above steps will produce the following output.

pic4

To understand this better, add totals and subtotals into the chart. To do this, go to the Analysis tab and select Totals, followed by Add All Subtotals.

pic5

Completing the steps above will generate the following output.

p6

The output above shows that the percent of total calculation is done across the different segments for each quarter of the given year. For example, in the first quarter of year 2016, the consumer, corporate, and home office segments contributed 29.37%, 19.23%, and 51.40% of sales, respectively. The insight is that the home office sales contribution is much higher than the other two segments in this quarter.

In the output above, you can see that the percent of total is calculated across the segments. It is also possible to visualize the quarterly data across columns. To do this, click on the Sales variable, which is placed into the Text option of the Marks pane, and select the option Compute Using. This is shown below.

pic7

You can see that there are multiple options available, and the default is set to Table (across). You can click on the Edit Table Calculation option to select a computing option and add secondary table calculations.

p8

Performing the steps discussed above will open a tab with multiple options, as shown below.

p9

The sections below explain a couple of these options in more detail. To begin, drag the Sales measure again into the Text option of the Marks shelf. You can see that there are two sales measures. The one with the delta sign indicates a table calculation, while the other one is the actual measure.

Selecting the Table (Down) option explained above, the following output is created.

p10

The output above shows that each cell of a column is divided by the total of that column to arrive at the cell percentages. For instance, the first-quarter sales in the year 2016 are 22,004, which is divided by the total of the consumer segment, which is 679,673. This gives the result of 3.24% for this cell.

In the example above, you selected the Table (Down) option. If you select the Pane option, the following output will be created.

p11

The pane option considers all the cells individually and divides them by the total pane value. For instance, the first-quarter sales in the year 2016 are 22,004, which is divided by the total pane value of 575,456. This gives the result of 3.82% for this cell.

To calculate other quick table calculations, such as running total, go to Quick Table Calculation and select Running Total. The first step for performing this calculation is to drag Order Date into the Columns shelf and make it continuous. Next, drag the Sales measure into the Rows shelf and select Running total from the quick table calculation.

p12

Next, drag actual sales and sales table calculated above to the Marks shelf in the text. Filter the years to select 2016 and 2017. Finally, select Bar from the Marks shelf.

p13

In the output above, the first number indicates the sales in that quarter, while the second number indicates the running total. This way it is easy to visualize actual and running totals for the quarter.

0