_edited_edited.png)
In Tableau, when the provided data is insufficient or not in the required format, we create calculated fields using various formulas to achieve the desired output. However, not all calculations need to be manually written because Tableau offers built-in calculations known as "Quick Table Calculations."
Before diving into quick table calculations, it's essential to understand partitioning and addressing, as they are fundamental to comprehending any type of calculation.
Partitioning: This refers to the dimension that sets the boundaries for a table calculation. In other words, it defines the panes within the table where the calculation will restart. For example, if Region and Category are dimensions in your table, these dimensions act as partitioning fields, setting the boundaries. When we apply calculations, Tableau uses partitioning to determine which subset of the data the calculation should apply to.
 Partitioning by region & category
Addressing: These are the dimensions that determine the direction in which the table calculation runs. Tableau offers easy-to-use options by right-clicking the measure and selecting ‘Compute Using.’ These options allow you to specify the direction for your calculation (e.g., Table Down, Table Across). By default, the addressing feature is set to Table Across.
Table (Across): This option calculates values across the entire table row. For example, if we have profit data for different years, it calculates profit across all years for each category.
Table (Down): This option calculates values down the table column-wise. So, if you have profit data for different categories listed in rows, it will calculate the profit for each year across all categories.
As table calculations become more complex, you may need to bypass the easy-to-use ‘Compute Using’ options and manually define your addressing and partitioning fields using ‘Specific Dimensions.’ This can be done by right-clicking the measure, selecting ‘Edit Table Calculation,’ and then choosing ‘Specific Dimensions’ under ‘Compute Using.’
The checked dimensions become the addressing fields, while any unchecked boxes become the partitioning fields. For example, if Category and Ship Mode are addressing fields and Year of Order is the partitioning field, the calculation transforms the percentage of total from each category across all ship modes within a year. The calculation restarts for each new year.
Now, let’s explore the quick table calculations available in Tableau.
1. Running Total: This calculation is used to compute the cumulative total of a measure across a table structure or specific dimension. It can sum values, average values, or replace all values with the lowest or highest value, among other options. For example, consider sales information across years (Order Date) and sub-categories. By applying a running total on sales, we can see the cumulative total at the row level. The visualization clearly shows the running sum of total sales compared to the individual sales values, indicating how each year’s sales are added to the previous years’ sales.
2 Difference: This table calculation computes the difference between the current value and another value in the table for each mark in the visualization. This calculation involves two values: the current value and the reference value from which the difference is calculated. Typically, the reference value is the previous value in the partition, but it can also be the next value, the first value, or the last value.
Previous: Calculates the difference between the current value and the previous value in the partition. This is the default setting.
Next: Calculates the difference between the current value and the next value in the partition.
First Value: Calculates the difference between the current value and the first value in the partition.
Last Value: Calculates the difference between the current value and the last value in the partition.
In the visualization below, it computes the difference between the current value and the first value (the difference value is shown in a blue box) and addresses the table (down).
3.Percent Difference: A Percent Difference From table calculation computes the difference between the current value and another value in the table as a percentage for each mark in the visualization. It is similar to the Difference calculation but expressed as a percentage.
4.Percent of Total: This calculation expresses each value as a percentage of the total for a specific dimension or for the entire table. This is particularly useful for understanding the relative contribution of each value to the overall total.
5.Moving Calculation: Moving calculations allow you to perform computations on a specific window or range of data points within your visualization. This is particularly useful for analyzing trends, smoothing out data fluctuations, and identifying patterns over time. It achieves this by performing an aggregation (such as sum, average, minimum, or maximum) across a specified number of values before and/or after the current value. These are sometimes referred to as rolling calculations. By default, Tableau uses Moving Average.
Let’s look at an example of a moving average. I’m examining daily sales using a dual-axis chart and applying a moving average by setting the window size to the previous 45 days and the next 45 days. This reveals hidden patterns, such as lower sales around May and increasing sales around November.
6.Rank Calculation: Rank calculations assign a rank to each row or value based on a specified measure.
There are many more table calculation applications and functions available in Tableau, but I hope this blog will help kickstart your understanding of the basics of quick table calculations.
Comments