Analytics-DA-201 - Explore and Analyze Data (41% of the exam) - Section 2.9
Write FIXED, INCLUDE, EXCLUDE, and nested level of detail (LOD) calculations.
Practice question for this objective
lock_openFree sampleExplore and Analyze Datahard
An analyst writes a nested LOD: { FIXED [Region] : AVG( { FIXED [Region], [Customer ID] : SUM([Sales]) } ) }. The data has many customers per region. What does the outer expression return for each Region?
AThe average Sales per customer within the Region, found by averaging each customer's total.check_circle Correct
BThe total Sales for the Region, because the inner SUM and the outer FIXED both aggregate at the Region grain.
CThe number of distinct customers in the Region, because nesting FIXED expressions counts the inner grain's members.
DThe average of individual order-line Sales within the Region, identical to a plain AVG([Sales]) by Region.
A nested LOD lets an outer aggregate operate on subtotals produced by an inner LOD at a finer grain. The inner FIXED defines a finer grain (Region and Customer ID) and returns each customer's total sales. The outer FIXED at Region grain then takes the AVG of those per-customer totals, yielding average sales per customer per Region, which differs from averaging raw order lines.
Why A is correct: The inner FIXED produces one SUM(Sales) per customer per Region, and the outer FIXED then averages those customer totals to give mean sales per customer in each Region.
Why B is wrong: The inner expression is at the finer Region-plus-Customer grain, so the outer AVG averages those per-customer totals rather than summing back to the Region total.
Why C is wrong: Nesting does not convert a SUM into a count, so the result is an average of sales amounts, not a tally of customers.
Why D is wrong: A plain AVG([Sales]) averages order lines, but here the inner SUM first rolls sales up to the customer level, so frequent buyers are weighted differently.
Answers that look right on this material and are not. Each one is a distractor from a different question in the Analytics-DA-201 bank for this domain.
Tableau evaluates the outer expression first to set a coarse value, then divides it across the inner dimensions.
errorWhy it is wrong: Evaluation runs inner before outer, not outer before inner; an LOD expression cannot divide a coarse total down to finer dimensions, so this inverts the actual flow.
{ INCLUDE [Segment] : SUM([Sales]) }, because INCLUDE pins the grain to [Segment] and then rolls those values up to the Region row.
errorWhy it is wrong: INCLUDE adds [Segment] to a grain that already contains it, so it just returns each region-segment cell's own sales, not the region total.
{ FIXED [Customer ID] : AVG( COUNTD([Order ID]) ) }, which averages the distinct order count inside the expression for each customer.
errorWhy it is wrong: Averaging a single per-customer count returns that same count, so the expression yields each customer's order count rather than the cross-customer average the analyst wants.