Tableau Training & Certification
- 22k Enrolled Learners
- Live Class
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:
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:
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 Calculations||Tableau 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.
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:
Table 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:
Here’s how the view looks after choosing the Running total option of table calculation:
Tableau calculations rely on two types of fields:
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):
Here’s how the crosstab looks after changing the addressing / compute using to Table (Down):
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.
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.
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.
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.
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])
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)
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.
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.
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.
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.
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.
RUNNING_SUM(SUM([Profit])) computes the running sum of SUM(Profit)
Size() – Returns the number of rows in the partition.
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.