Aggregate vs row-level calculations in Tableau - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the total sales using an aggregate function.

Tableau

SUM([1])

Drag options to blanks, or click blank then click option'

ASales

BAVG(Sales)

CCOUNT(Sales)

DMIN(Sales)

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVG or COUNT inside SUM causes errors or wrong results.

Leaving the field name blank.

The SUM function adds all values in the Sales field to give total sales.

2fill in blank

medium

Complete the code to calculate the average sales per row.

Tableau

AVG([1])

Drag options to blanks, or click blank then click option'

ASUM(Sales)

BMAX(Sales)

CCOUNT(Sales)

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Putting SUM(Sales) inside AVG causes wrong calculation.

Using COUNT instead of the field name.

The AVG function calculates the average of the Sales field values.

3fill in blank

hard

Fix the error in the calculation to get the total sales per category.

Tableau

SUM([1])

Drag options to blanks, or click blank then click option'

ASales

BAVG(Sales)

CCOUNT(Category)

DCategory

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to sum a text field like Category.

Using COUNT or AVG inside SUM.

To get total sales per category, sum the Sales field, not the Category field.

4fill in blank

hard

Fill both blanks to calculate the average sales per region, excluding zero sales.

Tableau

AVG(IF [1] > 0 THEN [2] END)

Drag options to blanks, or click blank then click option'

ASales

BRegion

DCategory

Attempts:

3 left

💡 Hint

Common Mistakes

Using Region or Category in the condition or average instead of Sales.

Not excluding zero sales properly.

The condition checks if Sales > 0, then averages the Sales values.

5fill in blank

hard

Fill all three blanks to create a calculation that sums sales only for the 'East' region.

Tableau

SUM(IF [1] = '[2]' THEN [3] ELSE 0 END)

Drag options to blanks, or click blank then click option'

ARegion

BEast

CSales

DCategory

Attempts:

3 left

💡 Hint

Common Mistakes

Using Category instead of Region in the condition.

Using Category instead of Sales to sum.

Not quoting the region name 'East'.

This calculation sums Sales only when Region equals 'East'. Otherwise, it adds zero.

Practice

1. Which statement best describes the difference between aggregate and row-level calculations in Tableau?

easy

A. Row-level calculations are only used for filtering data, aggregate calculations are for calculations.

B. Aggregate calculations work on each individual record, while row-level calculations summarize data.

C. Both aggregate and row-level calculations always summarize data across multiple records.

D. Row-level calculations operate on each individual data record, while aggregate calculations summarize multiple records.

Solution

1. Step 1: Understand row-level calculations

   Row-level calculations are applied to each individual row or record in the data source.

2. Step 2: Understand aggregate calculations

   Aggregate calculations combine or summarize multiple rows into a single value, like sum or average.

3. Final Answer:

   Row-level calculations operate on each individual data record, while aggregate calculations summarize multiple records. -> Option D

4. Quick Check:

   Row-level = individual rows, Aggregate = summary [OK]

Hint: Remember: row-level = each row, aggregate = summary [OK]

Common Mistakes:

• Confusing which calculation works on individual rows
• Thinking aggregate works on single records
• Mixing filtering with calculation types

2. Which of the following is the correct syntax for a row-level calculation in Tableau?

easy

A. [Sales] * 1.1

B. SUM([Sales])

C. AVG([Profit])

D. COUNTD([Customer ID])

Solution

1. Step 1: Identify row-level calculation syntax

   Row-level calculations use fields directly without aggregation functions, e.g., multiplying a field by a number.

2. Step 2: Identify aggregate calculation syntax

   Functions like SUM(), AVG(), COUNTD() are aggregate calculations summarizing data.

3. Final Answer:

   [Sales] * 1.1 -> Option A

4. Quick Check:

   Row-level uses direct field references without aggregation [OK]

Hint: Row-level calculations use fields directly, no SUM or AVG [OK]

Common Mistakes:

• Using aggregation functions for row-level calculations
• Confusing SUM() as row-level
• Not recognizing direct field references

3. Given a dataset with sales records, what will the Tableau calculation SUM([Sales]) / COUNT([Order ID]) return?

medium

A. The average sales per order (aggregate calculation).

B. The total sales multiplied by the number of orders (row-level calculation).

C. The sales value for each individual order (row-level calculation).

D. The count of unique sales values (aggregate calculation).

Solution

1. Step 1: Analyze the calculation components

   SUM([Sales]) adds all sales values; COUNT([Order ID]) counts all orders.

2. Step 2: Understand the division result

   Dividing total sales by number of orders gives average sales per order, an aggregate summary.

3. Final Answer:

   The average sales per order (aggregate calculation). -> Option A

4. Quick Check:

   SUM/COUNT = average per order [OK]

Hint: SUM divided by COUNT usually means average [OK]

Common Mistakes:

• Thinking the result is row-level instead of aggregate
• Confusing COUNT with COUNTD (unique count)
• Assuming multiplication instead of division

4. You wrote the calculation SUM([Sales] * [Quantity]) in Tableau but it gives an error. What is the likely problem?

medium

A. SUM() cannot be used with numeric fields.

B. You cannot multiply fields inside an aggregate function; multiply first, then aggregate.

C. You must use AVG() instead of SUM() for multiplication.

D. The calculation should be SUM([Sales]) * SUM([Quantity]) to work.

Solution

1. Step 1: Understand calculation order in Tableau

   Tableau requires row-level operations before aggregation; multiplying fields inside SUM() is invalid.

2. Step 2: Correct approach for multiplication then aggregation

   Multiply [Sales] by [Quantity] at row-level, then aggregate the result with SUM.

3. Final Answer:

   You cannot multiply fields inside an aggregate function; multiply first, then aggregate. -> Option B

4. Quick Check:

   Row-level calc inside aggregate must be done outside first [OK]

Hint: Multiply fields first, then aggregate with SUM [OK]

Common Mistakes:

• Trying to multiply inside SUM() directly
• Using SUM() on non-numeric fields
• Replacing SUM() with AVG() incorrectly

5. You want to calculate the average profit per customer in Tableau. Which calculation correctly combines row-level and aggregate calculations?

hard

A. SUM([Profit] / COUNTD([Customer ID]))

B. AVG(SUM([Profit]))

C. SUM([Profit]) / COUNTD([Customer ID])

D. SUM([Profit]) * COUNTD([Customer ID])

Solution

1. Step 1: Understand the goal

   We want average profit per customer, so total profit divided by unique customers.

2. Step 2: Analyze each option

   SUM([Profit]) / COUNTD([Customer ID]) divides total profit (SUM) by distinct customer count (COUNTD), correctly calculating average profit per customer.

3. Final Answer:

   SUM([Profit]) / COUNTD([Customer ID]) -> Option C

4. Quick Check:

   Total profit ÷ unique customers = average profit per customer [OK]

Hint: Divide total profit by distinct customers for average [OK]

Common Mistakes:

• Using AVG(SUM()) which is invalid syntax
• Dividing inside SUM() instead of outside
• Multiplying instead of dividing