Aggregations in Calculations - Tableau

1. Which calculation returns the average revenue per order at the current level of detail?
   Answer: SUM([Sales]) / COUNTD([Order ID]), returning $300$ When a question asks for a metric "per order," you need to identify the right denominator — and that means distinguishing between counting distinct entities versus counting raw rows. There are 5 total line items across 3 distinct orders (O1, O2, O3), with total sales of $100 + 200 + 150 + 50 + 400 = 900$. Average revenue per order means dividing that total by the number of unique orders: $900 \div 3 = 300$. That's exactly what A does — SUM([Sales]) / COUNTD([Order ID]) uses COUNTD (count distinct) to count 3 unique orders, giving $300$. This is the correct answer. B is wrong because AVG([Sales]) averages at the row level, dividing total sales by the number of line items ($900 \div 5 = 180$). That gives you average revenue per line, not per order — a fundamentally different granularity. C makes the same granularity mistake as B, but more explicitly. COUNT([Order ID]) counts every row (5 rows), not distinct orders, so $900 \div 5 = 180$ again. The trap here is that COUNT and COUNTD look similar but behave very differently when rows repeat an ID. D divides by COUNTD([Customer ID]), which counts 2 customers, yielding $900 \div 2 = 450$. That's average revenue per customer, not per order — a different aggregation level entirely. The key study tip: whenever you see "per [entity]," always ask whether you need COUNT (rows) or COUNTD (unique values). On Tableau exams, confusing these two is one of the most common traps.
2. What value does COUNT([Return ID]) / COUNTD([Order ID]) return, assuming standard numeric division?
   Answer: $1.5$, because three non-null returns are divided by two distinct orders When working with aggregate functions in Tableau, you need to carefully track which records each function counts — and COUNT vs. COUNTD behave very differently around nulls and duplicates. COUNT([Return ID]) counts non-null values in the Return ID field. Looking at the four records, the Return IDs are R1, null, R2, and R3 — so three non-null values, giving COUNT = 3. COUNTD([Order ID]) counts distinct non-null values. The Order IDs are O1, O1, O2, and null — two distinct non-null values (O1 and O2), giving COUNTD = 2. The expression therefore evaluates to $3 / 2 = 1.5$, confirming B is correct. Choice A is wrong because it confuses COUNT([Return ID]) with a total row count. Counting all four records ignores how COUNT skips nulls — it never reaches 3 returns "out of four records" in the way A describes. Choice C incorrectly assumes both functions somehow filter to only "matched" rows; there's no join logic here, just independent aggregations over all four records. Choice D misreads COUNT([Return ID]) as a row count (4), which would only be true if you used COUNT(*) or counted a field with no nulls. A reliable study tip: whenever you see COUNT and COUNTD together, pause and explicitly list out what each one sees. COUNT drops nulls; COUNTD drops nulls and duplicates. Writing out the values as a quick table — just like above — prevents you from conflating row counts with field-level aggregations.
3. Which calculation produces the required overall profit margin?
   Answer: SUM([Profit]) / SUM([Sales]), returning $0.11$ When calculating a ratio across multiple rows in Tableau, you need to think carefully about the order of operations — specifically, whether aggregation happens before or after division. The correct way to compute overall profit margin is to sum all profits, then divide by total sales: $\frac{20 + 90}{100 + 900} = \frac{110}{1000} = 0.11$. This is exactly what C does — SUM([Profit]) / SUM([Sales]) aggregates both measures first, then performs the division, giving you a true blended margin. A is the most tempting trap. AVG([Profit] / [Sales]) computes each row's margin individually — $\frac{20}{100} = 0.20$ and $\frac{90}{900} = 0.10$ — then averages them: $\frac{0.20 + 0.10}{2} = 0.15$. This treats both transactions as equally weighted, ignoring that the second transaction has nine times the sales volume. It answers "what is the average of the two margins?" not "what is the combined margin?" B makes the same row-level division mistake as A, but sums the per-row margins instead of averaging them: $0.20 + 0.10 = 0.30$. This has no meaningful business interpretation and inflates the result. D uses AVG([Profit]) / SUM([Sales]), mixing aggregation levels inconsistently. It divides the average profit $(55)$ by total sales $(1000)$, yielding $0.055$ — a number that doesn't represent any useful metric. The key study tip: whenever you need a ratio of totals, always use SUM() / SUM(). Applying division before aggregation (row-level) produces a weighted-average distortion that silently gives wrong answers.
4. Which calculation correctly produces the required value when Category is not placed on any view shelf?
   Answer: SUM(IF [Category] = "Furniture" THEN [Sales] ELSE 0 END) When working with Tableau calculations, you need to understand the difference between row-level and aggregate expressions — this question tests exactly that distinction. Tableau processes calculations at two levels: row-level (evaluated for each underlying transaction row before aggregation) and aggregate (evaluated after rows are grouped into a mark). The key insight here is that when multiple rows are compressed into a single mark, a row-level filter inside a SUM lets you cherry-pick which rows contribute to the total. Option A is correct because IF [Category] = "Furniture" THEN [Sales] ELSE 0 END runs at the row level — for each transaction row, it returns [Sales] if that row is Furniture, otherwise 0. Wrapping this in SUM() then totals only the Furniture values across all rows in the mark, regardless of what other categories are present. This is the classic FIXED-filter-inside-aggregate pattern. Option B is wrong because it places the IF outside the aggregate. Tableau cannot evaluate IF [Category] = "Furniture" at the mark level when Category is not on a shelf — [Category] is still a row-level field, making this a mixed-level expression that will throw an error. Option C fails for a similar reason: ATTR([Category]) returns a single value only if all rows in the mark share the same category. Since a mark contains multiple categories here, ATTR() returns an asterisk (*), so the condition is never TRUE. Option D has the same structural flaw as C — MIN([Category]) picks just one category value from the mark, so marks containing mixed categories will silently return 0 instead of the correct Furniture total. Study tip: Whenever you need to filter rows before aggregating, put the condition inside the aggregate — SUM(IF ... END) — not outside it.
5. Which calculation returns total revenue of $70$ for the two rows?
   Answer: SUM([Unit Price] * [Quantity]), returning $70$ When calculating a derived metric like revenue, the order in which you apply aggregations versus row-level calculations matters enormously in Tableau. Ask yourself: does the math need to happen before or after aggregation? Revenue is a row-level concept — for each transaction, you multiply that row's unit price by that row's quantity. Row 1 contributes $10 \times 1 = 10$, and Row 2 contributes $20 \times 3 = 60$, giving a correct total of $10 + 60 = 70$. Option C, SUM([Unit Price] * [Quantity]), does exactly this: it computes the product for each row first, then sums those products — producing $70$. This is the correct answer. Option A, SUM([Unit Price]) * SUM([Quantity]), aggregates each field independently before multiplying: $(10 + 20) \times (1 + 3) = 30 \times 4 = 120$. This inflates revenue because it treats every unit price as if it applies to every quantity. Option B, AVG([Unit Price]) * SUM([Quantity]), uses the average price of $15$ times total quantity of $4$, yielding $60$ — closer, but wrong because averaging the price discards the actual price-quantity pairing. Option D, AVG([Unit Price] * [Quantity]), correctly multiplies at the row level but then averages the results instead of summing them: $(10 + 60) / 2 = 35$, which understates total revenue. The key study tip: whenever a metric requires multiplying two fields together, always perform the multiplication inside the aggregation — SUM([A] * [B]) — not between two separate aggregations. Separating the aggregations breaks the row-level relationship between the fields.
6. Which calculation returns the proportion of all distinct customers who are online purchasing customers?
   Answer: COUNTD(IF [Channel] = "Online" AND [Sales] > 0 THEN [Customer ID] END) / COUNTD([Customer ID]) When calculating a proportion in Tableau, you need the right aggregation on both sides of the division: the numerator must count unique qualifying customers, and the denominator must count unique total customers. The key distinction here is between COUNT (counts all rows, including duplicates) and COUNTD (counts distinct values). The definition states an online purchasing customer has at least one online transaction with sales > 0. Looking at the data: C1, C2, C3, and C4 are all four distinct customers. C1, C2, and C3 have qualifying online-with-sales transactions, but C4's online transaction has zero sales. So the answer should be 3 out of 4, or 0.75. Answer D correctly uses COUNTD(IF [Channel] = "Online" AND [Sales] > 0 THEN [Customer ID] END) in the numerator, which counts distinct customers meeting the condition (C1, C2, C3 = 3), divided by COUNTD([Customer ID]), which counts all distinct customers (4). Result: 0.75 ✓ Answer A uses COUNT in the numerator instead of COUNTD, so C1 would be counted twice (two qualifying rows), giving 4/4 = 1.0 — an inflated, incorrect proportion. Answer B flips the aggregation logic: COUNTD on the numerator is right, but COUNT([Customer ID]) in the denominator counts all seven transaction rows, not distinct customers, giving 3/7 ≈ 0.43 — wrong denominator entirely. Answer C inverts the fraction — distinct qualifying customers should be the numerator, not the denominator. This would give 4/3, which exceeds 1.0 and is meaningless as a proportion. Study tip: On Tableau calculation questions, ask yourself two things: "Do I need distinct values?" (use COUNTD) and "Is the fraction oriented correctly?" Proportion questions almost always require COUNTD on both sides, with the filtered condition in the numerator.
7. Which calculated field applies the threshold at the required level?
   Answer: IF SUM([Sales]) > 1000 THEN "High Volume" ELSE "Standard" END Whenever you see a question about aggregation in Tableau, ask yourself: at what level of detail does the logic need to apply? Here, the business rule is about combined regional sales — meaning you need a measure that totals all transactions within a region before comparing to the threshold. SUM([Sales]) does exactly this. It aggregates every transaction row belonging to a region into a single combined value, then tests whether that total exceeds $1{,}000$. If a region has ten transactions of $150$ each, SUM([Sales]) correctly yields $1{,}500$ and labels it "High Volume." Option B is the correct answer. Option A uses AVG([Sales]), which computes the mean transaction value. Those same ten transactions of $150$ would average to $150$, well below the threshold — a region could have enormous combined sales yet never be labeled "High Volume." Average measures individual-transaction magnitude, not cumulative volume. Option C wraps an inner IF [Sales] > 1000 condition inside the sum, which pre-filters out any transaction below $1{,}000$ before summing. Since the passage explicitly states no individual transaction exceeds $1{,}000$, this inner filter eliminates everything, producing a null sum that fails the threshold — the opposite of what you need. Option D uses MAX([Sales]), which returns only the single largest transaction in the region. Like the average, it tests individual transaction size rather than combined volume, so it would never flag a region built on many small transactions. Study tip: On Tableau questions about business thresholds, always map the rule to the right aggregation — if the threshold applies to a total, reach for SUM, not AVG or MAX.
8. Which calculation returns the required average score of $2$?
   Answer: SUM([Score]) / COUNT([Response ID]) When a question asks you to calculate an average with specific rules about missing values, you need to think carefully about what goes in the numerator versus what goes in the denominator — they don't always come from the same field. Here, the business rule is clear: treat null scores as zero and keep all submitted responses in the denominator. That means you need the total score across all responses (nulls counting as zero) divided by the total number of submitted responses. With scores of $5$, null ($= 0$), and $1$, the numerator is $5 + 0 + 1 = 6$ and the denominator is $3$, giving $6 / 3 = 2$. Option A does exactly this: SUM([Score]) ignores nulls in addition but that's fine — Tableau sums $5 + 1 = 6$ — and COUNT([Response ID]) counts all three non-null Response IDs, giving $6 / 3 = 2$. That's your answer. Option B, AVG([Score]), automatically excludes nulls from both the sum and the count, computing $6 / 2 = 3$ — wrong because the missing response disappears entirely. Option C, SUM([Score]) / COUNT([Score]), has the right numerator but COUNT([Score]) also skips nulls, giving $6 / 2 = 3$ for the same reason as B. Option D, COUNT([Score]) / COUNT([Response ID]), produces $2 / 3$, which counts non-null scores rather than summing them — a completely different calculation. The key pattern to remember: whenever nulls must be treated as zero but still counted, you need COUNT on a field that is never null (like an ID column) as your denominator, not COUNT or AVG on the value field itself.
9. What does the calculation AVG([Sales]) - AVG([Cost]) return?
   Answer: $95$, because each average excludes nulls independently When you see a calculation like AVG([Sales]) - AVG([Cost]) in Tableau, the key is recognizing that each aggregate function operates independently before the subtraction happens. Tableau computes each AVG separately, ignoring nulls only within its own column. Here's how the math works. For Sales, the two non-null values are $100$ and $200$, giving $AVG = \frac{100 + 200}{2} = 150$. For Cost, the two non-null values are $60$ and $50$, giving $AVG = \frac{60 + 50}{2} = 55$. The result is $150 - 55 = 95$, confirming that C is correct. A is wrong because it assumes only rows where both fields are non-null contribute — as if Tableau performs row-level pairing before aggregating. That would be the logic of AVG([Sales] - [Cost]), a row-level calculation, not two separate AVGs. B is wrong on two counts: it mischaracterizes the operation as summing rather than averaging, and $190$ doesn't follow from any valid interpretation of the data. D is wrong because it divides by the number of non-null Sales rows (2) after computing only a single row's difference ($100 - 60 = 40$), mixing row-level and aggregate logic incorrectly. The critical study tip here: always distinguish between row-level expressions like [Sales] - [Cost] (which pairs values before aggregating) and aggregate expressions like AVG([Sales]) - AVG([Cost]) (which aggregates each field independently). This distinction appears frequently on the Tableau exam and is a common source of errors.
10. What distinct-customer value should Tableau display in the grand total?
    Answer: $3$, because the distinct count is recomputed across all regions Whenever you see a question about grand totals with COUNTD in Tableau, remember this core principle: grand totals are not computed from the visible subtotals — they are recomputed from the underlying row-level data. In this scenario, the East region has customers A and B ($COUNTD = 2$), and West has customers B and C ($COUNTD = 2$). When Tableau calculates the grand total for COUNTD([Customer ID]), it goes back to the full dataset and counts every distinct customer across all regions: A, B, and C. Customer B appears in both regions but is only counted once, giving you $3$ — confirming that D is correct. Choice A assumes Tableau simply adds the regional subtotals ($2 + 2 = 4$), but that would double-count customer B, who appears in both regions. Summation of subtotals only works correctly for additive measures like SUM, not for set-based operations like COUNTD. Choice B suggests averaging the subtotals ($\frac{2+2}{2} = 2$), which has no basis in how Tableau computes grand totals — no averaging occurs here. Choice C claims the answer is $1$ because only one customer overlaps between regions, but overlap count is not what COUNTD measures; it measures the size of the union of all distinct values, not the intersection. A good rule of thumb for the Tableau exam: **COUNTD grand totals always recompute from scratch on the full data**. If you ever see a question asking what a grand total will display for a non-additive measure, resist the urge to aggregate the subtotals — Tableau doesn't take that shortcut.

Start this quiz