Why calculations extend data analysis in Tableau - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a calculated field that sums sales.

Tableau

SUM([1])

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

ASales

BProfit

CQuantity

DDiscount

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'Profit', 'Quantity', or 'Discount' instead of 'Sales'.

The SUM function adds all values in the 'Sales' field, which is common in data analysis.

2fill in blank

medium

Complete the code to calculate average profit per order.

Tableau

AVG([1])

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

ASales

BOrder ID

CQuantity

DProfit

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Order ID' which is not numeric and cannot be averaged.

AVG calculates the average of the 'Profit' field, useful to understand profit per order.

3fill in blank

hard

Fix the error in the calculation to find profit ratio.

Tableau

SUM([1]) / SUM(Sales)

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

AQuantity

BDiscount

CProfit

DOrder ID

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Discount' or 'Quantity' which do not represent profit.

The profit ratio is total profit divided by total sales, so SUM(Profit) is correct.

4fill in blank

hard

Fill both blanks to create a calculated field for profit margin percentage.

Tableau

(SUM([1]) / SUM([2])) * 100

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

AProfit

BSales

CQuantity

DDiscount

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping profit and sales or using non-monetary fields.

Profit margin is profit divided by sales, multiplied by 100 to get a percentage.

5fill in blank

hard

Fill all three blanks to create a calculated field that counts orders with profit greater than zero.

Tableau

COUNT(IF [1] > [2] THEN [3] END)

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

AProfit

B0

COrder ID

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Sales' instead of 'Profit' in the condition.

Counting 'Sales' instead of 'Order ID'.

This counts orders where profit is greater than zero by checking 'Profit > 0' and counting 'Order ID'.

Practice

1. What is the main reason to use calculations in Tableau for data analysis?

easy

A. To create new data fields from existing data

B. To change the color of charts

C. To delete unwanted data rows

D. To export data to Excel

Solution

1. Step 1: Understand the purpose of calculations

   Calculations in Tableau allow you to create new data fields by using existing data, which helps in deeper analysis.

2. Step 2: Compare other options

   Changing colors, deleting rows, or exporting data are not the main purposes of calculations.

3. Final Answer:

   To create new data fields from existing data -> Option A

4. Quick Check:

   Calculations create new data fields [OK]

Hint: Calculations create new data, not just change visuals [OK]

Common Mistakes:

• Thinking calculations only change chart colors
• Confusing calculations with data export
• Believing calculations delete data rows

2. Which of the following is the correct way to create a calculated field in Tableau?

easy

A. Click File > Export > Calculated Field

B. Right-click in Data pane > Create > Calculated Field

C. Double-click on a worksheet title

D. Drag a dimension to the Filters shelf

Solution

1. Step 1: Recall how to create calculated fields

   In Tableau, you create calculated fields by right-clicking in the Data pane and selecting Create > Calculated Field.

2. Step 2: Eliminate incorrect options

   Double-clicking worksheet title, exporting files, or dragging dimensions to Filters shelf do not create calculated fields.

3. Final Answer:

   Right-click in Data pane > Create > Calculated Field -> Option B

4. Quick Check:

   Right-click Data pane to create calculation [OK]

Hint: Right-click Data pane to add calculations fast [OK]

Common Mistakes:

• Trying to create calculations from worksheet title
• Confusing export with calculation creation
• Using Filters shelf instead of Data pane

3. Given this Tableau calculation: IF [Sales] > 1000 THEN 'High' ELSE 'Low' END, what will be the result for a sale of 1500?

medium

A. 'Low'

B. 1500

C. 'High'

D. Error

Solution

1. Step 1: Understand the IF statement logic

   The calculation checks if Sales is greater than 1000. If true, it returns 'High', else 'Low'.

2. Step 2: Apply the condition to the value 1500

   Since 1500 > 1000, the condition is true, so the result is 'High'.

3. Final Answer:

   'High' -> Option C

4. Quick Check:

   1500 > 1000 means 'High' [OK]

Hint: Check condition true or false to pick result [OK]

Common Mistakes:

• Choosing numeric value instead of string result
• Confusing 'High' and 'Low' outputs
• Assuming calculation causes error

4. Identify the error in this Tableau calculation: IF [Profit] > 0 THEN 'Gain' ELSE 'Loss'

medium

A. Missing END keyword to close IF statement

B. Incorrect field name 'Profit'

C. Using ELSE instead of ELSEIF

D. No error, calculation is correct

Solution

1. Step 1: Check IF statement syntax

   Tableau IF statements must end with END keyword to close the block.

2. Step 2: Verify the calculation

   The calculation lacks END at the end, causing a syntax error.

3. Final Answer:

   Missing END keyword to close IF statement -> Option A

4. Quick Check:

   IF statements need END keyword [OK]

Hint: Always end IF with END keyword [OK]

Common Mistakes:

• Forgetting END keyword
• Assuming ELSEIF is required here
• Thinking field name is wrong without checking

5. You want to create a calculation in Tableau that classifies customers as 'Top' if their total sales are above the average sales of all customers, and 'Other' otherwise. Which calculation correctly achieves this?

hard

A. IF SUM([Sales]) > TOTAL(AVG([Sales])) THEN 'Top' ELSE 'Other' END

B. IF SUM([Sales]) > AVG(SUM([Sales])) THEN 'Top' ELSE 'Other' END

C. IF [Sales] > AVG([Sales]) THEN 'Top' ELSE 'Other' END

D. IF SUM([Sales]) > WINDOW_AVG(SUM([Sales])) THEN 'Top' ELSE 'Other' END

Solution

1. Step 1: Understand the need for comparing to average sales

   We want to compare each customer's total sales to the average total sales across all customers.

2. Step 2: Identify correct Tableau functions

   WINDOW_AVG(SUM([Sales])) computes the average of total sales over the window (all customers), which is correct here.

3. Step 3: Check other options

   IF SUM([Sales]) > AVG(SUM([Sales])) THEN 'Top' ELSE 'Other' END uses AVG(SUM([Sales])) which is invalid syntax. IF [Sales] > AVG([Sales]) THEN 'Top' ELSE 'Other' END compares row-level sales to average, not total sales. IF SUM([Sales]) > TOTAL(AVG([Sales])) THEN 'Top' ELSE 'Other' END misuses TOTAL with AVG.

4. Final Answer:

   IF SUM([Sales]) > WINDOW_AVG(SUM([Sales])) THEN 'Top' ELSE 'Other' END -> Option D

5. Quick Check:

   Use WINDOW_AVG for average over all customers [OK]

Hint: Use WINDOW_AVG for average across all rows [OK]

Common Mistakes:

• Using AVG(SUM()) which is invalid
• Comparing row sales to average without aggregation
• Misusing TOTAL function with AVG