In this Tableau tutorial I will discuss a basic quick table calculation and try to demystify what is happening behind the scenes. All of the following will hopefully be made more clear in the video but I’m sharing the text below for reference after you watch the video.
I can’t take credit for the content in this post as the Tableau online help site has some quality information on table calculations which I will reference below. There are two important concepts in understanding table calculations: partitioning and addressing.
Key Concepts with Table Calculations: Addressing vs. Partitioning
The dimensions that define how to group the calculation (the scope of data it is performed on) are called partitioning fields. The table calculation is performed separately within each partition.
Partitioning fields break the view up into multiple sub-views (or sub-tables), and then the table calculation is applied to the marks within each such partition.
The remaining dimensions, upon which the table calculation is performed, are called addressing fields, and determine the direction of the calculation.
The direction in which the calculation moves (for example, in calculating a running sum, or computing the difference between values) is determined by the addressing fields.
So when you order the fields in the Specific Dimensions section of the Table Calculation dialog box from top to bottom, you are specifying the direction in which the calculation moves through the various marks in the partition.
When you add a table calculation using the Compute Using options, Tableau identifies some dimensions as addressing and others as partitioning automatically, as a result of your selections.
But when you use Specific Dimensions, then it’s up to you to determine which dimensions are for addressing and which for partitioning.
At the level (Partitioning)
The At the level option is only available when you select Specific Dimensions in the Table Calculations dialog box, and when more than one dimension is selected in the field immediately below the Compute Using options —that is, when more than one dimension is defined as an addressing field.
This option is not available when you’re defining a table calculation with Compute Using, because those values establish partitions by position. But with Specific Dimensions, because the visual structure and the table calculation are not necessarily aligned, the At the level option is available to let you fine-tune your calculation.
Use this setting to set a break (that is, restart of the calculation) in the view, based on a particular dimension. How is this different from just using that dimension for partitioning? In fact, it is partitioning, but it’s partitioning by position rather than by value, which is how partitioning is defined with the Compute Using options.
Filtering on Table Calculations in Tableau
Filtering on Table Calculations in Tableau can be a bit hacky. More often than not, table calculations are dependent upon the data in the view. That means in order to filter on a table calculation, we need a way to preserve underlying data and only hide data from the view.
Filters based on table calculations do not filter out underlying data in the data set, because table calculation filters are applied last in the order of operations. Instead, the data is hidden from the view, allowing dimension members to be hidden from the view without impacting the data in the view.
Notice in the order of operations diagram below how Dimension and Measure filters are applied before Table calculation filters. When trying to filter on a table calculation (which is dependent upon the data in the view) the results may be unexpected. If we turn our dimension or measure into a table calculation, we can then filter the Table calculation at the appropriate level, which preserves underlying data and only hides the table calculation values we wish to filter from the view.
The content in this post was quoted from the sources below.
As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.
All views and opinions are solely my own and do NOT necessarily reflect those my employer.