Following on from our first series instalment, we go into one of the most important distinctions between table calculations and level of detail calculations: where they are calculated.
Level of Detail Calculations: The Data Source
Level of detail calculations perform their calculations based on the data source, i.e whatever data tableau is connected to. This means it accesses from the lowest level of granularity in the data. You can then nest LODs to create any level of granularity to calculate from.
The only “exception” to calculating at the data source level is if there are extract, data source or context filters. All LODs will calculate after these filters have been applied to the data source. Include and Exclude will further be restricted in their calculations by a dimension filter.
Table Calculations: The View
Table calculations perform their calculations based upon the view.
The view is an aggregated version of the data source created in a tableau worksheet. It is aggregated based on which dimensions are on the shelves and marks. The lowest level of granularity in the view is decided by the unique combinations of dimensions in view.
i.e. State and County: North Carolina, Orange County; California, Orange County… etc.
The most important concept is that the view is just a table. Dimensions placed on the shelves and cards drive the number of rows in the table feeding the view. Continuous measures consolidate the row values into to the granularity of the view by an aggregation method (i.e. summation).
You can essentially access the table feeding the view by selecting all the points on the sheet (ctrl+a), right click, view data and look at the summary tab table.
Since table calculations are built on top of the view they can only calculate at the level of the dimensions used in view (or higher, but not lower).
For table calculations, this often means that more thought has to be put into how you want to structure the view. The view and a nested LOD have equivalence. A nested LOD is like building a view table and working from that. Whereas a table calculation by default works from a summarised table. This is why and how I was able to build the Table Calculation to LOD conversion seen in the first part of this series:
Without risking me very quickly getting into too much more detail I will stop here for now. I will be sure to add a more unstructured/natural piece of how I understand table calculations and LODs in their entirety at the end of this series as a kind of overview.
Until next time folks!