Tableau Quiz: Aggregations In Calculations

10 questions · exam conditions

0:00

Aggregations In CalculationsQuestion 1 of 10

A view displays distinct customer counts by Region. East contains customers A and B, while West contains customers B and C. A grand total is enabled for COUNTD([Customer ID]).

What distinct-customer value should Tableau display in the grand total?

4

, because the two regional distinct counts are added

2

, because the regional distinct counts are averaged

1

, because only one customer occurs in both regions

3

, because the distinct count is recomputed across all regions

← Back to quizzes

Tableau Quiz

Tableau Quiz: Aggregations In Calculations

Practice Aggregations In Calculations in Tableau with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Aggregations In Calculations, giving you a quick way to practice the rules, question types, and explanations that matter most for Tableau.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A view displays distinct customer counts by Region. East contains customers A and B, while West contains customers B and C. A grand total is enabled for COUNTD([Customer ID]).

What distinct-customer value should Tableau display in the grand total?

$4$ , because the two regional distinct counts are added
$2$ , because the regional distinct counts are averaged
$1$ , because only one customer occurs in both regions
$3$ , because the distinct count is recomputed across all regions (correct answer)

Explanation: 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.

Question 2

Four records have Order ID values O1, O1, O2, and null. Their Return ID values are R1, null, R2, and R3, respectively.

What value does COUNT([Return ID]) / COUNTD([Order ID]) return, assuming standard numeric division?

$0.75$ , because three returns are divided by four records
$1.5$ , because three non-null returns are divided by two distinct orders (correct answer)
$1.0$ , because two matched return records are divided by two orders
$2.0$ , because four records are divided by two distinct orders

Explanation: 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.

Question 3

A view contains two transaction rows. The first has sales of $100$ and profit of $20$ . The second has sales of $900$ and profit of $90$ . The analyst needs the overall profit margin for both transactions combined.

Which calculation produces the required overall profit margin?

AVG([Profit] / [Sales]), returning $0.15$
SUM([Profit] / [Sales]), returning $0.30$
SUM([Profit]) / SUM([Sales]), returning $0.11$ (correct answer)
AVG([Profit]) / SUM([Sales]), returning $0.055$

Explanation: 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.

Question 4

A data source contains one row per order line. Order O1 has line sales of $100$ and $200$ , order O2 has line sales of $150$ , and order O3 has line sales of $50$ and $400$ . O1 and O2 belong to customer C1; O3 belongs to customer C2.

Which calculation returns the average revenue per order at the current level of detail?

SUM([Sales]) / COUNTD([Order ID]), returning $300$ (correct answer)
AVG([Sales]), returning $180$
SUM([Sales]) / COUNT([Order ID]), returning $180$
SUM([Sales]) / COUNTD([Customer ID]), returning $450$

Explanation: 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.

Question 5

Three submitted survey responses have non-null Response IDs. Their Score values are $5$ , null, and $1$ . For this metric, a missing score must be treated as zero while every submitted response remains in the denominator.

Which calculation returns the required average score of $2$ ?

SUM([Score]) / COUNT([Response ID]) (correct answer)
AVG([Score])
SUM([Score]) / COUNT([Score])
COUNT([Score]) / COUNT([Response ID])

Explanation: 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.

Question 6

A worksheet has one mark per Region and multiple transaction rows per mark. A calculated field must return Furniture sales only, even when the current mark also contains rows from other categories.

Which calculation correctly produces the required value when Category is not placed on any view shelf?

SUM(IF [Category] = "Furniture" THEN [Sales] ELSE 0 END) (correct answer)
IF [Category] = "Furniture" THEN SUM([Sales]) ELSE 0 END
IF ATTR([Category]) = "Furniture" THEN SUM([Sales]) ELSE 0 END
IF MIN([Category]) = "Furniture" THEN SUM([Sales]) ELSE 0 END

Explanation: 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.

Question 7

Three rows contain Sales and Cost pairs of $100$ and $60$ , $200$ and null, and null and $50$ , respectively.

What does the calculation AVG([Sales]) - AVG([Cost]) return?

$40$ , because only the row with both values contributes
$190$ , because total cost is subtracted from total sales
$95$ , because each average excludes nulls independently (correct answer)
$20$ , because the paired difference is divided by two sales values

Explanation: 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.

Question 8

A transaction data source has two rows. The first row has a unit price of $10$ and a quantity of $1$ . The second has a unit price of $20$ and a quantity of $3$ . Revenue is not stored and must be calculated.

Which calculation returns total revenue of $70$ for the two rows?

SUM([Unit Price]) * SUM([Quantity]), returning $120$
AVG([Unit Price]) * SUM([Quantity]), returning $60$
SUM([Unit Price] * [Quantity]), returning $70$ (correct answer)
AVG([Unit Price] * [Quantity]), returning $35$

Explanation: 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.

Question 9

A data set has seven transaction rows. C1 has two positive online transactions; C2 has two positive store transactions; C3 has one positive online and one positive store transaction; and C4 has one online transaction with zero sales. An analyst defines an online purchasing customer as a customer with at least one online transaction having sales greater than zero.

Which calculation returns the proportion of all distinct customers who are online purchasing customers?

COUNT(IF [Channel] = "Online" AND [Sales] > 0 THEN [Customer ID] END) / COUNTD([Customer ID])
COUNTD(IF [Channel] = "Online" AND [Sales] > 0 THEN [Customer ID] END) / COUNT([Customer ID])
COUNTD([Customer ID]) / COUNTD(IF [Channel] = "Online" AND [Sales] > 0 THEN [Customer ID] END)
COUNTD(IF [Channel] = "Online" AND [Sales] > 0 THEN [Customer ID] END) / COUNTD([Customer ID]) (correct answer)

Explanation: 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.

Question 10

A worksheet has one mark per Region and multiple transaction rows per mark. A region should be labeled High Volume when its combined sales exceed $1{,}000$ , even if no individual transaction exceeds $1{,}000$ .

Which calculated field applies the threshold at the required level?

IF AVG([Sales]) > 1000 THEN "High Volume" ELSE "Standard" END
IF SUM([Sales]) > 1000 THEN "High Volume" ELSE "Standard" END (correct answer)
IF SUM(IF [Sales] > 1000 THEN [Sales] END) > 1000 THEN "High Volume" ELSE "Standard" END
IF MAX([Sales]) > 1000 THEN "High Volume" ELSE "Standard" END

Explanation: 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.