Creating calculated fields in Tableau - Business Scenario Walkthrough

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to analyze profit margins by product category and region. They ask you to create calculated fields to find the profit margin percentage and then build a summary report.

📊 Data: You have sales data including Product Category, Region, Sales Amount, and Profit Amount for each transaction.

🎯 Deliverable: Create calculated fields for Profit Margin Percentage and build a summary table showing average profit margin by Product Category and Region.

Sample Data

Product Category	Region	Sales Amount	Profit Amount
Electronics	North	1000	200
Electronics	South	1500	300
Furniture	North	800	160
Furniture	South	1200	240
Clothing	North	700	70
Clothing	South	900	90
Electronics	East	1100	220
Furniture	East	950	190
Clothing	East	600	60

1

Step 1: Open Tableau and connect to the sales data source.

No formula needed.

Expected Result

Data is loaded and visible in Tableau's Data pane.

2

Step 2: Create a calculated field named 'Profit Margin %'.

[Profit Amount] / [Sales Amount] * 100

Expected Result

A new field 'Profit Margin %' appears in the Data pane.

3

Step 3: Build a summary table: Drag 'Product Category' to Rows, 'Region' to Columns.

No formula needed.

Expected Result

Rows show product categories, columns show regions.

4

Step 4: Drag the 'Profit Margin %' calculated field to the Text mark to show average profit margin.

Aggregation: AVG([Profit Margin %])

Expected Result

Table cells display average profit margin percentages by category and region.

5

Step 5: Format the 'Profit Margin %' values to show one decimal place and add a % sign.

Format numbers as Percentage with 1 decimal place.

Expected Result

Profit margin values display as percentages, e.g., 20.0%.

Final Result

-------------------------------------------------
| Product Category | North  | South  | East  |
-------------------------------------------------
| Electronics      | 20.0%  | 20.0%  | 20.0% |
| Furniture        | 20.0%  | 20.0%  | 20.0% |
| Clothing         | 10.0%  | 10.0%  | 10.0% |
-------------------------------------------------

✓Electronics and Furniture have a consistent profit margin of 20% across all regions.

✓Clothing has a lower profit margin of 10% in all regions.

✓Profit margins are stable and do not vary by region for these categories.

Bonus Challenge

Create a calculated field that categorizes profit margin into 'High' if above 15%, 'Medium' if between 10% and 15%, and 'Low' if below 10%. Then, build a color-coded heatmap by Product Category and Region using this classification.

Show Hint

Use an IF or CASE statement in the calculated field to assign categories based on [Profit Margin %]. Then use color marks in Tableau to visualize.

Practice

1. What is the main purpose of creating a calculated field in Tableau?

easy

A. To change the color of a chart

B. To import data from external sources

C. To create new data values based on existing data using formulas

D. To delete rows from the data source

Solution

1. Step 1: Understand what calculated fields do

   Calculated fields allow you to create new data by applying formulas to existing data.

2. Step 2: Compare options to this definition

   Only To create new data values based on existing data using formulas describes creating new data values using formulas, which matches the purpose of calculated fields.

3. Final Answer:

   To create new data values based on existing data using formulas -> Option C

4. Quick Check:

   Calculated fields = new data from formulas [OK]

Hint: Calculated fields create new data from old data using formulas [OK]

Common Mistakes:

• Confusing calculated fields with data import
• Thinking calculated fields change visuals only
• Assuming calculated fields delete data

2. Which of the following is the correct syntax to create a calculated field that adds 10 to the value of the field Sales?

easy

A. SUM(Sales) + 10

B. [Sales] + 10

C. {Sales} + 10

D. Sales + 10

Solution

1. Step 1: Recall Tableau field reference syntax

   In Tableau, fields are referenced inside square brackets like [Sales].

2. Step 2: Check each option's syntax

   [Sales] + 10 uses [Sales] + 10, which is correct. Sales + 10 misses brackets, C uses curly braces which are incorrect here, and A uses aggregation which is not needed for simple addition.

3. Final Answer:

   [Sales] + 10 -> Option B

4. Quick Check:

   Field names need brackets in formulas [OK]

Hint: Use square brackets around field names in formulas [OK]

Common Mistakes:

• Omitting square brackets around field names
• Using curly braces instead of brackets
• Adding aggregation unnecessarily

3. Given the calculated field formula IF [Profit] > 0 THEN 'Profit' ELSE 'Loss' END, what will be the result for a record where [Profit] is -50?

medium

A. 'Loss'

B. 'Profit'

C. 50

D. Error

Solution

1. Step 1: Understand the IF condition

   The formula checks if [Profit] is greater than 0. If yes, returns 'Profit', else returns 'Loss'.

2. Step 2: Apply the condition to the value -50

   Since -50 is not greater than 0, the ELSE part applies, so the result is 'Loss'.

3. Final Answer:

   'Loss' -> Option A

4. Quick Check:

   Profit > 0? No, so 'Loss' [OK]

Hint: Check IF condition carefully for each value [OK]

Common Mistakes:

• Confusing greater than with less than
• Expecting numeric output instead of text
• Ignoring ELSE clause

4. Identify the error in this calculated field formula: IF [Sales] > 1000 THEN 'High' ELSE 'Low'

medium

A. Incorrect use of square brackets around Sales

B. No error, formula is correct

C. Using text values instead of numbers

D. Missing END keyword to close IF statement

Solution

1. Step 1: Review IF statement syntax in Tableau

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

2. Step 2: Check the given formula

   The formula lacks the END keyword at the end, so it will cause a syntax error.

3. Final Answer:

   Missing END keyword to close IF statement -> Option D

4. Quick Check:

   IF statements need END keyword [OK]

Hint: Always end IF statements with END [OK]

Common Mistakes:

• Forgetting END keyword in IF formulas
• Misplacing square brackets
• Confusing text and numeric outputs

5. You want to create a calculated field that categorizes sales into three groups: 'Low' for sales below 500, 'Medium' for sales between 500 and 1000, and 'High' for sales above 1000. Which formula correctly implements this?

hard

A. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] <= 1000 THEN 'Medium' ELSE 'High' END

B. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] < 1000 THEN 'Medium' ELSE 'High' END

C. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] < 1000 THEN 'Medium' ELSEIF [Sales] > 1000 THEN 'High' END

D. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] <= 1000 THEN 'Medium' ELSEIF [Sales] > 1000 THEN 'High'

Solution

1. Step 1: Understand the sales ranges

   Sales below 500 = 'Low', 500 to 1000 inclusive = 'Medium', above 1000 = 'High'.

2. Step 2: Check each formula for correct conditions and syntax

   IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] <= 1000 THEN 'Medium' ELSE 'High' END correctly uses ELSEIF with <= 1000 for 'Medium' and ends with END. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] < 1000 THEN 'Medium' ELSE 'High' END excludes 1000 from 'Medium'. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] < 1000 THEN 'Medium' ELSEIF [Sales] > 1000 THEN 'High' END has an extra ELSEIF but no final ELSE. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] <= 1000 THEN 'Medium' ELSEIF [Sales] > 1000 THEN 'High' misses END keyword.

3. Final Answer:

   IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] <= 1000 THEN 'Medium' ELSE 'High' END -> Option A

4. Quick Check:

   Ranges inclusive and END keyword correct [OK]

Hint: Use ELSEIF and END; include boundary with <= for ranges [OK]

Common Mistakes:

• Missing END keyword
• Incorrect boundary conditions (e.g., excluding 1000)
• Using multiple ELSEIF without final ELSE