In Tableau, there are two methods that I usually use for aggregations. One is Table Calculation which I wrote 2 blogs about. The first part of my Table Calculation blog is about the dimension of Table Calculation in Tableau. The second part of the Table Calculation blog is about the Specific Dimension in the Table Calculation window and how to write Table Cals in the Calculated Field in Tableau.
Besides the Table Calculation in Tableau, another powerful calculation that most data analysts use in Tableau is LOD (Level of Detail). In this blog, I am going to present:
1/ What is LOD?
2/ Which main LOD expressions are used in Tableau and how to create it in the Calculated Field?
3/ The order of LOD expressions in the Tableau's Order of Operation
1/ What is LOD?
LOD or LOD expressions are known as Level of Detail expressions which help Tableau users to compute the values at the data source level and visualization level.
In another way, when working with many levels or hierarchies, LOD expressions can control the granularity at a specific level users want.
For example: I apply LOD in calculating:
- Total Sales of each year
- Count how many orders in each year
- Which month has the highest sales amount
- and much more ...
2/ Main LOD Expressions in Tableau
In Tableau, there are 3 main LOD Expressions: FIXED, INCLUDE, and EXCLUDE. In most cases, I usually use FIXED LOD Expression to compute the value at the level I want.
FIXED LOD Expression
Syntax: {FIXED <Dimension 1>, <Dimension 2>, ... : <expression to aggregate>}
In some cases, if the user would like to aggregate all levels of the view, they could put the <expression to aggregate> in the curly brackets. {<expression to aggregate>}.
For example: I use SuperStore sample data in Tableau. I have Category level, Sub-Category lower level, and SUM([Sales]) discrete values in Rows. (Image 1)
As you can see, for each category, there are many sub-categories. The Sales value represents each sub-category row level.
Goal 1: I would like to calculate the total sales of each category. It means for Furniture Category, the total sale = Chairs (335,768) + Tables (208,020) + Bookcases (115,361) + Furnishings (95,598) = 754,747.
I will create a calculated field to type my FIXED LOD expression. (Image 2)
In the calculated field with the title "Total Sales for each Category", I type the LOD Expression: {FIXED [Category]: SUM([Sales])}. For each Category, I calculate the total Sales where Category is the dimension and SUM of sales is my aggregation.
Then, click on Apply and OK. Drag the "Total Sales for each Category" pill to Rows and change it to Discrete value. The table shows that the Total Sales for Furniture is 754,748 (the last digit differs from the number I calculated above because I formatted the number in decimal).
Goal 2: I would like to calculate the total sales of all categories in the view.
I created a new Calculated Field with the title "Total Sales". I want to calculate the total sales of all categories, so in this case, I don't need to type FIXED and the specific dimension. Instead, I type {SUM([Sales])}. Tableau will understand that I want to calculate the total sales of the view. In the Total Sales column, I got 2,326,534 for each row level.
INCLUDE LOD Expression
Syntax: {INCLUDE <Dimension 1>, <Dimension 2>, ...: <expression to aggregate>}
Besides the dimension in the current view, the INCLUDE LOD expression computes the values in additional dimension(s).
For example: I would like to calculate the Average sales of each Category by Region.
As I just introduced the FIXED LOD expression above, I would create a calculated field with the expression: AVG({FIXED [Region], [Category]: SUM([Sales])}) (Image 4)
If I take the average of Furniture for all Region, the expression would be (164,538+212,232+117,299+260,680)/4 = 188,687
However, I don't need the Region level on Rows for the INCLUDE Expression. I created a calculated field with the expression: AVG({INCLUDE [Region]: SUM([Sales])}) (Image 5)
In this case, the Category was in the Row, so I only need to INCLUDE an additional dimension Region. The result of the Furniture category is the same as the result with FIXED LOD expression using 2 dimensions.
EXCLUDE LOD Expression
Syntax: {EXCLUDE <Dimension 1>, <Dimension 2>, ...: <expression to aggregate>}
In contrast with the INCLUDE LOD Expression, the EXCLUDE LOD Expression helps to eliminate the dimension from the lower granularity level and calculate the high granularity level.
For Example: there are 2 dimensions - Region and Category in the Row. I would like to calculate the average sales of each Category without the higher Region level.
I will create a Calculated Field to exclude the Region dimension. I will need an expression: AVG({EXCLUDE [Region]:SUM([Sales])})
3/ The Order of LOD Expression in Tableau's Order of Operation
In Tableau, there is an order of operation where the filter will be in order. In image 7 below, the FIXED LOD has more priority than the other 2 LOD expressions.
Note that: according to the Tableau page, FIXED LOD computes the value using the specified dimensions, without reference to the dimensions in the view. Fixed LOD expressions are computed before the dimension filters apply. However, the Include/ Exclude LOD expressions need to add more dimensions/remove the dimension in the view.
After this blog, I hope you understand what is LOD, the 3 main types of LOD expressions, and the order of operation of LOD expression in Tableau. LOD Expressions and Table Calculations are used the most in Tableau to compute the values based on the dimension. Depending on the purpose of calculation from the user, LOD Expressions are useful in some cases. In the LOD Expressions - Part 2, I will introduce some useful cases that use LOD Expressions to compute values in Tableau.
See you in the next blog!