Overview of Tableau Table Calculation
Tableau is one of the best BI tools in the market and it can handle large amounts of data sets. In this article, we would see Table Calculation which simplifies in various operations. In this topic, we are going to learn about Tableau Table Calculation.
Table calculations are computations that are applied to the values in the table and it is dependent on the table structure or rather the dimensions that are in the viz or the visualization level of detail. So, whenever you want to extend the values present in your table or your view, these calculations can be used.
An important point to remember is that the table calculations are dependent on the dimensions in the VIS level of detail. So, what exactly is this level of detail? The level of detail includes all those dimensions that are present in the shelves or the cards as indicated here within the red boxes.
These are the below topics which we would cover today
- Tableau Table Calculations
- Tableau Calculation Fields
- Difference between Tableau table calculations and Tableau calculated Fields
- Tableau Calculation Function
Tableau Table Calculations
Table Calculation comes are running several types like total, percentage, total percentage, average, total average in the table and so on. These are predefined calculations and called a Tableau table calculation.
The benefits of Table Calculation
- The fastest way to calculate the values for the tables like total, average or percent, etc.,
- It can also be saved and use as calculated fields
- Efficient result for calculating values
Tableau Calculation Fields
Before begin with this topic start your Tableau desktop and download the given dataset to male this practice.
Steps to download Tableau Desktop: Click here
Download Bank Customer Dataset: Click here
Open a new workbook in tableau desktop and connect the data sources using the .xls file dataset
The data sources look like
Using this data source we can create a table based on customer name and total balance
Table calculation would add the measures in the view. The easiest way to make the calculation is to explore the Quick Table Calculation option when we select the dropdown like below
After running the total, the table will look like below
The calculation can rely on these two types
- Addressing field – It says the direction of the calculation
- Partitioning fields – The data can be partition into various types and then act as a calculation
- In this example, we have seen the running total is being from top to bottom based on the addressing field “name”. The calculation by default taken as the name category field.
- When we add the region into the field, it is easy to change the addressing field
Let’s do this, drag the region in the column and click the measure table calculation again. Now we can identify the data symbol Compute using and change how we want the calculation to be computed.
We can also change the partitioning in different ways based on the needs
Difference Between Table Calculation and Calculated Fields
Both table calculation and calculated fields are the same using the compute the result. But we will see the difference how the compute talks place and how it can be reused in more worksheets
1. Tableau Table Calculation
- It is created as locally and lives in tableau view
- When we say it would create as locally and can’t be reused to the data source
- If we want to reuse, we need to save in the pane by dragging it
2. Tableau Calculated Field
- It will be created as a separated column in the data source
- The source can’t be changed, so we can use the calculated field in any of the worksheets
- It would be computed with the data sources and view as a new column and can be re-use
When we compare to table calculation and calculated field, the calculation has a limited scope then calculated field. A calculated field can be enabled for deeper analysis.
Tableau Calculation Function
Let’s see few Tableau Calculation Functions
Lookup value returns the values of the expression for the selected target and pointing the relative offset in the current row. It will also return the NULL values if the target values don’t contain anything.
Syntax for lookup(): LOOKUP(expression,[offset])
Example: LOOKUP(SUM([BALANCE])), +1)
The index which returns a value from the given array range. When we take a simple example in excel by indexing A1: B12 and returning the value by using the MATCH function. In the tableau, it will partition and returns the current row value
Example: INDEX() = 5
It will return the no. of rows in the current row with the partition of the last row
Example: Current row index is 7 of 10 LAST() = 8
It will return the no. of rows in the current row with the partition of the first row
Example: Current row index is 7 FIRST() = -5
It will return the values from the previous row which has been calculated. Also, return the expression when the current row is the first row in the table
Syntax for Previous_Value(): PREVIOUS_VALUE(expression)
Example: SUM([BALANCE]) * PREVIOUS_VALUE (2)
In the viz, we can see the rank for every row or pane in the table calculation. It will return the rank for the current partition. The NULL Value can be ignored in the rank function.
It will return the maximum for the given row from the first-row partition.
Example: RUNNING_MAX (SUM[BALANCE])
It will return the minimum for the given row from the first-row partition.
Example: RUNNING_MIN (SUM[BALANCE])
It will return the Average value for the given row from the first-row partition.
Example: RUNNING_AVG (SUM[BALANCE])
It will return the count of value for the given row from the first-row partition.
Example: RUNNING_COUNT (SUM[BALANCE])
It will return the sum of the value for the given row from the first-row partition
Example: RUNNING_SUM (SUM[BALANCE])
It will say the no. of rows in the partition.
Example: SIZE() = 10 , When the partition have 10 rows
It will return the total of a given row by ROW/COLUMN.
Now in this article, we have learned what is about table calculation, calculated field difference between these two and how to use table calculations.
This is a guide to Tableau Table Calculation. Here we discuss overview of tableau table calculation, table calculation field, calculated field and how to use table calculations. You may also have a look at the following articles to learn more –