In your workbook, sometimes you want to break down data into more details in order to gain better insights as per dimension. A measure can be calculated differently each time you choose a different level of dimension as basis. For example, the average cost of buying a product per person differs as the method on which it is grouped by changes. If the cost is grouped by Name first, the average cost should be devided between 2 people (Harry and Ron) which is 151.25. On the other hand, it will be 100.83 if we want to see the average cost based on each purchase date (3 dates). Finally, if the normal average cost per record would be 60.50.
Tableau introduced the Level of detail (LOD) to help users establish such detailed calculated measures at the data source level as well as at the visualization level. There are three types of LOD Expression: Include (more granular level), Exclude (less granular level) and Fixed (independent level).
This blog aims to help users somehow understand the LOD Fixed Expression with the following syntax:
{[ FIXED ] < declaration of the dimension > : <expression to aggregate>}
How to create LOD Expression:
Step 1: Set up Vizualization
1. Open Tableau Desktop and connect to your saved data source.
2. Navigate to a new worksheet.
3. From the Data pane, under Dimensions, drag Subcategorie to the Rows Shelf.
4. From the Data pane, under Measures, drag Sales to the Columns Shelf.
5. In the measure sum(Sales) click on drop down menu and select Table Calculation/Percentage per total.
A bar chart showing the percentage sales for each subcategory appears.
Step 2: Create LOD Fixed Expression
1. Select Analysis > Create Calculated Field.
2. In the Calculation editor that opens, do the following:
Name the calculation, LOD fixed Sale.
Enter the following LOD expression.
3. When finished, click ok.
Step 3: Use the LOD expression in visualization
1. From the Data pane, under Measures, drag LOD fixed Sales to the Columns shelf and place it to the left of SUM(Sales).
2. On the Filter container, drag Subcategory over, then right click, choose show filter.
3. From the Sum(Sales) Mark Card, drag and drop Sum(Sales) on the Label.
4. From the LOD Fixed Sales Mark Card, drag and drop LOD Fixed Sales on the Label.
Before, when all items in Subcategory are chosen, there is no difference between the value of percentage sales in both charts.
However, meanwhile some items in the filter are deselected, the percentage of sales per subcategory on the left changes dynamically, the figures (values on label) in the right remain the same. This is due to the fixed feature of LOD expression, which keeps the total sales unchanged regardless of variation in the dimension filter. The shapes of the two charts look alike. The total of all values in the left chart equals to 100%, on the other hand, the left chart’s values add up to less than 100%.
