Data Visualization with Tableau (31 Blogs) Become a Certified Professional
AWS Global Infrastructure

BI and Visualization

Topics Covered
  • Pentaho BI (9 Blogs)
  • Microsoft BI (1 Blogs)
  • Data Visualization with Tableau (29 Blogs)
  • BI and Data Visualization with Qlikview (1 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

How To Best Utilize Tableau Table Calculations?

Published on Aug 30,2019 22 Views

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

Tableau is indeed one of the best data visualization tools in the market. We see many people wanting to get Tableau Certification to make it big in the BI market. The credit for above situation is largely due to the ease of functioning of Tableau offers. In this article we would be focusing on Tableau table calculation, which helps simplify various operations in Tableau.

These are the pointers we would be focusing on:

Let us get started then,

Tableau Table Calculations

Tableau comes with several preset calculations like running total, difference, percent difference, percent of total, moving average and more. These predefined calculations are called table calculations because they compute the result based on a virtual table that includes only numbers on the view.

 These provide several benefits including:

  • Fast way to create advanced calculations
  • Provides efficient solution for calculating results
  • Tableau calculations can be saved for future use as calculated fields

Moving on with this article on Tableau Table calculations

Difference between Table Calculations and Calculated Fields

Table Calculations and Calculated Fields are similar in the sense that they both use functions to compute the results. The difference is how and where the computing takes place, where the result is saved and if it can be reused in more worksheets.

Tableau Table CalculationsTableau Calculated Fields

Table Calculations live in tableau view and are created locally

Calculated Fields are created on a data level as a separate column in the data source

The Table Calculation stays locally where it’s created and is not send back to the data source to be re-used

Tableau doesn’t change the source but can create an extract where the calculations will be visible

To re-use a Table Calculation, we need to save it by dragging it in the Data Pane

Calculated Fields are created by computing with the data source and can be found in it as a new column. And can be re-used

The difference between the two types of calculations goes beyond where they are found. Table Calculations are simpler, and their scope is more limited compared to Calculated Fields. Calculated fields are much more diverse enabling deeper analysis.

Moving on with this article on Tableau Table calculations

Tableau Calculations Fields

Before we move further make sure you have tableau installed on your system to understand the above bit better. Consider this simple example created with Sample-Superstore data set:

Demo - Tableau Table Calculation - EdurekaTable calculations are added to measures, so in order to add a table calculation, click a measure that’s on the view. The fastest way to add a table calculation is to explore Quick table calculation and choose an option:

Demo - Tableau Table Calculation - Edureka

Here’s how the view looks after choosing the Running total option of table calculation:

Demo - Tableau Table Calculation - Edureka

Tableau calculations rely on two types of fields:

  • Partitioning fields: Data is partitioned into several buckets; each is then acted on by calculations
  • Addressing fields: Defines the direction of the calculation

Note

  • In the example above the running total is being computed from left to right, which is the default addressing. This would mean that, by default, the table calculation is being addressed by the Product Category dimension. 

  • This leaves the Month dimension as the partitioning field. For a running total calculation, this doesn’t make a lot sense. It is easy to change the addressing by changing how the table calculation is being computed. 

To do this, click on the measure with the table calculation again, now identified with a delta symbol, hover over compute using, and change how the calculation should be computed (or addressed):

Demo - Tableau Table Calculation - Edureka

Here’s how the crosstab looks after changing the addressing / compute using to Table (Down):

Demo - Tableau Table Calculation - Edureka

Now that the addressing field has been changed to Month and the partitioning field has been changed to Category. I can look at each Category column and look down across months to see how the sales built up throughout the year.

Moving on with this article on Tableau Table calculations

Tableau Calculation Functions

Here is the list of few Tableau table calculation functions – 

First(): Returns the number of rows from the current row to the first row in the partition. 

Example

When the current row index is 3, FIRST () = -2

Index() Returns the index of the current row in the partition, without any sorting with regard to value. The first-row index starts at 1.

Example

For the third row in the partition, INDEX () = 3

Last() Returns the number of rows from the current row to the last row in the partition.

Example

When the current row index is 3 of 7, LAST () = 4

Lookup() Returns the value of the expression in a target row, specified as a relative offset from the current row. Use FIRST () + n and LAST () – n as part of your offset definition for a target relative to the first/last rows in the partition. If offset is omitted, the row to compare to can be set on the field menu. This function returns NULL if the target row cannot be determined.

Syntax – LOOKUP (expression, [offset])

Example

LOOKUP(SUM([Profit]), FIRST () +2) computes the SUM(Profit) in the third row of the partition

Previous _Value () Returns the value of this calculation in the previous row. Returns the given expression if the current row is the first row of the partition.

Syntax: PREVIOUS_VALUE (expression)

Example

SUM([Profit]) * PREVIOUS_VALUE (1) computes the running product of SUM(Profit)

Rank() Returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the option ‘asc’ | ‘desc’ argument to specify ascending or descending order. Nulls are ignored in ranking functions. They are not numbered, and they do not count against the total number of records in percentile rank calculations.

Running_Avg () Returns the running average of the given expression, from the first row in the partition to the current row.

Example

RUNNING_AVG (SUM[Profit]) computes the running average of SUM(Profit)

Running_Count() Returns running count of the given expression, from the first row in the partition to the current row.

Example

RUNNING_COUNT (SUM[Profit]) computes the running count of SUM (Profit)

Running_Max() – Returns the running maximum of the given expression, from the first row in the partition to the current row. 

Example

RUNNING_MAX (SUM[Profit]) computes the running maximum of SUM (Profit)

Running_Min() Returns the running minimum of the given expression, from the first row in the partition to the current row.

Example

RUNNING_MIN(SUM([Profit])) computes the running minimum of SUM(Profit)

Running_Sum() Returns the running sum of the given expression, from the first row in the partition to the current row.

Example

RUNNING_SUM(SUM([Profit])) computes the running sum of SUM(Profit)

Size() Returns the number of rows in the partition.

Example

SIZE() = 5 when the current partition contains five rows.

Total() – Returns the total for the given expression in a table calculation partition. 

There are many, many more table calculations and functions within the powerhouse of Tableau. However, by deconstructing and seeing how these table calculation functions work, try to understand the machinery behind the scenes to build an effective visualization. The only way to compare a measure against itself is to use table calculations.

This brings us to the end of this article on Tableau Table calculations. If you wish to master Tableau, Edureka has a curated course on Tableau Training & Certification which covers various concepts of data visualization in depth.

Got a question for us? Please mention it in the comments section and we will get back to you at the earliest.

Comments
0 Comments

Browse Categories

Subscribe to our Newsletter, and get personalized recommendations.