Arithmetic calculations in Tableau - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the profit performance by calculating profit and profit margin for each product category.

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

🎯 Deliverable: Create a Tableau dashboard showing total sales, total cost, total profit, and profit margin by product category.

Sample Data

Product Category	Sales Amount	Cost Amount
Electronics	1200	800
Furniture	850	600
Clothing	600	400
Electronics	1500	1000
Furniture	700	500
Clothing	900	650
Electronics	1300	900
Furniture	950	700
Clothing	750	500

1

Step 1: Connect your sales data to Tableau and load the data source.

No formula needed; just connect the data source.

Expected Result

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

2

Step 2: Create a calculated field named 'Profit' to find the difference between Sales Amount and Cost Amount.

[Sales Amount] - [Cost Amount]

Expected Result

Profit is calculated for each row.

3

Step 3: Create a calculated field named 'Profit Margin' to calculate profit margin as a percentage.

SUM([Profit]) / SUM([Sales Amount])

Expected Result

Profit Margin is calculated as a proportion using total profit over total sales.

4

Step 4: Build a worksheet with 'Product Category' on Rows, and SUM of Sales Amount, SUM of Cost Amount, SUM of Profit, and Profit Margin on Columns.

Drag 'Product Category' to Rows; drag 'Sales Amount', 'Cost Amount', and 'Profit' to Columns and set aggregation to SUM; drag 'Profit Margin' to Columns (no aggregation needed as it is already aggregated).

Expected Result

Table shows total sales, total cost, total profit, and profit margin by product category.

5

Step 5: Format the Profit Margin column to show percentage with one decimal place.

Right-click 'Profit Margin' field > Format > Numbers > Percentage > 1 decimal place.

Expected Result

Profit Margin values display as percentages with one decimal.

6

Step 6: Create a dashboard and add the worksheet to it for presentation.

Drag the worksheet onto the dashboard canvas.

Expected Result

Dashboard displays the profit analysis by product category.

Final Result

Product Category | Sales Amount | Cost Amount | Profit | Profit Margin
-----------------------------------------------------------------------
Electronics      | 4000         | 2700        | 1300   | 32.5%
Furniture        | 2500         | 1800        | 700    | 28.0%
Clothing         | 2250         | 1550        | 700    | 31.1%

✓Electronics has the highest total sales and profit.

✓Furniture has the lowest profit margin among the categories.

✓Clothing has a strong profit margin close to Electronics.

Bonus Challenge

Add a filter to the dashboard to allow users to select specific product categories and see updated profit calculations.

Show Hint

Use a 'Product Category' filter on the dashboard and set it to show only relevant categories when selected.

Practice

1. In Tableau, which operator is used to multiply two fields [Sales] and [Quantity] to calculate total revenue?

easy

A. Use the * operator like [Sales] * [Quantity]

B. Use the + operator like [Sales] + [Quantity]

C. Use the / operator like [Sales] / [Quantity]

D. Use the - operator like [Sales] - [Quantity]

Solution

1. Step 1: Identify the arithmetic operation for total revenue

   Total revenue is calculated by multiplying sales price by quantity sold.

2. Step 2: Use the multiplication operator in Tableau

   Tableau uses the * symbol to multiply fields, so [Sales] * [Quantity] is correct.

3. Final Answer:

   Use the * operator like [Sales] * [Quantity] -> Option A

4. Quick Check:

   Multiplication = * [OK]

Hint: Multiply fields with * operator inside square brackets [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Forgetting to put field names in square brackets
• Using / or - operators incorrectly

2. Which of the following is the correct syntax to calculate the average sales per order in Tableau?

easy

A. SUM([Sales]) / COUNT([Order ID])

B. SUM([Sales]) * COUNT([Order ID])

C. AVG([Sales]) + COUNT([Order ID])

D. SUM([Sales]) - COUNT([Order ID])

Solution

1. Step 1: Understand average sales per order calculation

   Average sales per order equals total sales divided by number of orders.

2. Step 2: Use correct aggregation functions and operators

   Use SUM([Sales]) to get total sales and COUNT([Order ID]) to count orders, then divide.

3. Final Answer:

   SUM([Sales]) / COUNT([Order ID]) -> Option A

4. Quick Check:

   Average = SUM / COUNT [OK]

Hint: Divide total sales by order count using SUM and COUNT [OK]

Common Mistakes:

• Multiplying instead of dividing sales and order count
• Using AVG([Sales]) without counting orders
• Adding or subtracting instead of dividing

3. Given the following Tableau calculation:
SUM([Profit]) / SUM([Sales])
What does this calculation represent?

medium

A. The total profit plus total sales

B. The difference between profit and sales

C. The profit margin as a ratio of profit to sales

D. The average sales per profit

Solution

1. Step 1: Analyze the calculation components

   The formula divides total profit by total sales using SUM aggregation.

2. Step 2: Interpret the meaning of the ratio

   Profit divided by sales gives the profit margin ratio, showing profitability percentage.

3. Final Answer:

   The profit margin as a ratio of profit to sales -> Option C

4. Quick Check:

   Profit margin = SUM(Profit) / SUM(Sales) [OK]

Hint: Divide total profit by total sales to get profit margin [OK]

Common Mistakes:

• Thinking it adds or subtracts profit and sales
• Confusing ratio with average
• Ignoring aggregation functions

4. You wrote this Tableau calculation:
SUM([Sales] + [Profit])
But it gives an error. What is the correct way to fix it?

medium

A. Change to SUM([Sales]) * SUM([Profit])

B. Change to SUM([Sales]) + SUM([Profit])

C. Remove SUM and write [Sales] + [Profit]

D. Use AVG([Sales] + [Profit]) instead

Solution

1. Step 1: Understand aggregation rules in Tableau

   You cannot add fields inside SUM directly; each field must be aggregated separately.

2. Step 2: Correct the syntax by summing each field then adding

   Use SUM([Sales]) + SUM([Profit]) to sum each field first, then add results.

3. Final Answer:

   Change to SUM([Sales]) + SUM([Profit]) -> Option B

4. Quick Check:

   Aggregate separately, then add [OK]

Hint: Sum fields separately before adding [OK]

Common Mistakes:

• Adding fields inside SUM causing syntax error
• Multiplying sums instead of adding
• Removing aggregation causing errors

5. You want to create a calculated field in Tableau that shows the profit percentage of sales for each product category. Which formula correctly calculates this?

hard

A. AVG([Profit]) / AVG([Sales])

B. SUM([Profit] / [Sales]) * 100

C. SUM([Profit]) - SUM([Sales]) * 100

D. SUM([Profit]) / SUM([Sales]) * 100

Solution

1. Step 1: Understand profit percentage calculation

   Profit percentage is (total profit / total sales) times 100 to get percent.

2. Step 2: Use correct aggregation and order of operations

   Sum profit and sales separately, divide sums, then multiply by 100.

3. Final Answer:

   SUM([Profit]) / SUM([Sales]) * 100 -> Option D

4. Quick Check:

   Profit % = (SUM(Profit) / SUM(Sales)) * 100 [OK]

Hint: Divide summed profit by sales, multiply by 100 for percent [OK]

Common Mistakes:

• Dividing fields before summing causing wrong results
• Using AVG instead of SUM for totals
• Subtracting instead of dividing