Tableau Calculated Fields and LODs Questions and Answers 1 — Questions and Answers
Question 1: An analyst wants to create a bar chart showing the total sales for each Sub-Category. They also want to display a reference line showing the average sales for the corresponding product Category for comparison. The view contains [Category] and [Sub-Category] on the Rows shelf. Which calculation will correctly compute the average sales at the Category level, regardless of the Sub-Category detail in the view?
- SUM({EXCLUDE [Sub-Category] : AVG([Sales])})
- {INCLUDE [Category] : AVG([Sales])}
- WINDOW_AVG(SUM([Sales]))
- {FIXED [Category] : AVG([Sales])} (Correct answer)
Correct answer: {FIXED [Category] : AVG([Sales])}
A FIXED LOD expression computes a value using specified dimensions without reference to other dimensions in the view. `{FIXED [Category] : AVG([Sales])}` calculates the average sales for each Category and will return the same value for all sub-categories within that category, making it ideal for a reference line. EXCLUDE would be relative to the view, INCLUDE would make the calculation more granular, and WINDOW_AVG is a table calculation that operates on the values currently in the visualization.
Question 2: You are creating a view that shows Sales by [Region] and [State]. You need to calculate the percentage of total sales that each state contributed to its region. Which of the following LOD expressions would be most appropriate for the denominator in your percentage calculation (i.e., the total sales for the region)?
- {INCLUDE [State] : SUM([Sales])}
- {FIXED : SUM([Sales])}
- {EXCLUDE [State] : SUM([Sales])} (Correct answer)
- {FIXED [Region], [State] : SUM([Sales])}
Correct answer: {EXCLUDE [State] : SUM([Sales])}
The `EXCLUDE` LOD expression is ideal for 'percent of total' scenarios where you need to compute an aggregation at a less granular level than the view. Since the view is at the [State] level, `{EXCLUDE [State] : SUM([Sales])}` will remove the [State] dimension from the calculation's context, effectively summing sales at the [Region] level, which is the correct denominator.
Question 3: Which of the following best describes a key difference between a FIXED Level of Detail expression and an INCLUDE/EXCLUDE LOD expression in relation to filters?
- INCLUDE/EXCLUDE LODs are calculated before dimension filters, while FIXED LODs are calculated after.
- FIXED LODs ignore all filters, including Context Filters.
- FIXED LOD expressions are computed before regular dimension filters are applied, whereas INCLUDE/EXCLUDE expressions are computed after. (Correct answer)
- All LOD expressions are calculated after dimension filters in the Tableau Order of Operations.
Correct answer: FIXED LOD expressions are computed before regular dimension filters are applied, whereas INCLUDE/EXCLUDE expressions are computed after.
According to Tableau's Order of Operations, FIXED LOD expressions are computed before dimension filters. This means that unless the filter is added to context, a standard dimension filter will not affect the result of a FIXED LOD. In contrast, INCLUDE and EXCLUDE LOD expressions are calculated after dimension filters, so their results will be affected by them.
Question 4: A calculated field uses the `ATTR()` function on a dimension, like `ATTR([Customer Name])`. In a visualization, this field returns an asterisk (*). What does this signify?
- The [Customer Name] field contains a NULL value for the given mark.
- The calculation has an error and needs to be corrected.
- There is more than one distinct [Customer Name] associated with the data for that specific mark in the view. (Correct answer)
- The [Customer Name] field is being used as a continuous measure.
Correct answer: There is more than one distinct [Customer Name] associated with the data for that specific mark in the view.
The ATTR() function tests if a field has a single, unique value within the context of the mark. If MIN(dimension) = MAX(dimension), it returns that value. If there are multiple different values, it returns an asterisk (*) to indicate this.
Question 5: You need to create a calculated field to show the sales for the most recent year in the dataset. Which of the following calculations correctly isolates the sales for the latest year?
- IF YEAR([Order Date]) = YEAR(TODAY()) THEN [Sales] END
- IF YEAR([Order Date]) = MAX(YEAR([Order Date])) THEN [Sales] END
- IF ATTR(YEAR([Order Date])) = WINDOW_MAX(MAX(YEAR([Order Date]))) THEN SUM([Sales]) END
- IF YEAR([Order Date]) = {MAX(YEAR([Order Date]))} THEN [Sales] END (Correct answer)
Correct answer: IF YEAR([Order Date]) = {MAX(YEAR([Order Date]))} THEN [Sales] END
This calculation uses a table-scoped FIXED LOD `{MAX(YEAR([Order Date]))}` to find the single latest year in the entire dataset. It then compares each row's Order Date year to this fixed maximum year. This is a row-level calculation that correctly flags sales from only the most recent year. The other options are incorrect because `MAX(YEAR([Order Date]))` would be an aggregate and cause an error, `YEAR(TODAY())` assumes the data is current, and the `WINDOW_MAX` option is an unnecessarily complex table calculation.
Question 6: An analyst is comparing the performance of individual products. The view shows `SUM(Sales)` by `[Product Name]`. They want to add a column that shows the average sales for the `[Sub-Category]` that each product belongs to. What is the most direct way to create this calculation?
- {INCLUDE [Product Name] : AVG([Sales])}
- {FIXED [Sub-Category] : AVG([Sales])} (Correct answer)
- AVG([Sales])
- TOTAL(AVG([Sales]))
Correct answer: {FIXED [Sub-Category] : AVG([Sales])}
The goal is to compute an average at the `[Sub-Category]` level and display it for each product. A `FIXED` LOD is perfect for this because it calculates the average sales for each `[Sub-Category]` independently of the `[Product Name]` dimension in the view. This value will then be repeated for each product within the same sub-category.
Tableau Calculated Fields and LODs Questions and Answers 1
An analyst wants to create a bar chart showing the total sales for each Sub-Category.
They also want to display a reference line showing the average sales for the corresponding product Category for comparison.
The view contains [Category] and [Sub-Category] on the Rows shelf.
Which calculation will correctly compute the average sales at the Category level, regardless of the Sub-Category detail in the view?