Why calculations extend data analysis in Tableau - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants to understand how calculated fields can help reveal deeper insights from sales data beyond simple totals.

📊 Data: You have monthly sales data by product category and region, including sales amount and quantity sold.

🎯 Deliverable: Create a Tableau dashboard that uses calculated fields to show profit margin percentage and average sales per transaction, alongside total sales.

Sample Data

Month	Region	Category	Sales Amount	Quantity Sold	Cost Amount	Transactions
Jan	North	Electronics	12000	30	9000	25
Jan	South	Furniture	8000	20	6000	15
Feb	North	Electronics	15000	35	11000	30
Feb	South	Furniture	7000	18	5000	12
Mar	North	Electronics	13000	32	9500	28
Mar	South	Furniture	9000	22	6500	18
Jan	East	Electronics	11000	28	8500	22
Feb	East	Furniture	6000	15	4500	10
Mar	East	Electronics	14000	33	10000	27

1

Step 1: Connect the sales data to Tableau and create a new worksheet.

Import the sample data as a data source in Tableau.

Expected Result

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

2

Step 2: Create a calculated field named 'Profit Margin %' to show profit as a percentage of sales.

Profit Margin % = (SUM([Sales Amount]) - SUM([Cost Amount])) / SUM([Sales Amount]) * 100

Expected Result

A new field 'Profit Margin %' is available showing profit margin percentage for each data point.

3

Step 3: Create a calculated field named 'Average Sales per Transaction' to find average sales value per transaction.

Average Sales per Transaction = SUM([Sales Amount]) / SUM([Transactions])

Expected Result

A new field 'Average Sales per Transaction' is available showing average sales per transaction.

4

Step 4: Build a view with Rows = Month, Columns = Region, and Text showing SUM of Sales Amount.

Drag 'Month' to Rows, 'Region' to Columns, and SUM([Sales Amount]) to Text on Marks card.

Expected Result

Table shows total sales by month and region.

5

Step 5: Add 'Profit Margin %' and 'Average Sales per Transaction' as additional text fields in the view.

Drag 'Profit Margin %' and 'Average Sales per Transaction' to Tooltip or Text on Marks card.

Expected Result

Each cell shows sales amount, profit margin %, and average sales per transaction.

6

Step 6: Create a dashboard combining this worksheet with filters for Category and Region.

Add the worksheet to a new dashboard and add filter controls for 'Category' and 'Region'.

Expected Result

Dashboard allows interactive filtering and shows calculated insights alongside sales.

Final Result

-------------------------------------------------------------
| Month | North Sales | North Profit % | North Avg Sales Txn |
|-------|-------------|----------------|---------------------|
| Jan   | $12,000     | 25%            | $480                |
| Feb   | $15,000     | 26.7%          | $500                |
| Mar   | $13,000     | 26.9%          | $464                |
-------------------------------------------------------------
| Month | South Sales | South Profit % | South Avg Sales Txn |
|-------|-------------|----------------|---------------------|
| Jan   | $8,000      | 25%            | $533                |
| Feb   | $7,000      | 28.6%          | $583                |
| Mar   | $9,000      | 27.8%          | $500                |
-------------------------------------------------------------
| Month | East Sales  | East Profit %  | East Avg Sales Txn  |
|-------|-------------|----------------|---------------------|
| Jan   | $11,000     | 22.7%          | $500                |
| Feb   | $6,000      | 25%            | $600                |
| Mar   | $14,000     | 28.6%          | $519                |
-------------------------------------------------------------

✓Profit margin percentage reveals which regions and months are more profitable beyond just sales volume.

✓Average sales per transaction helps understand customer buying behavior and transaction value.

✓Calculations extend analysis by providing deeper insights that simple totals cannot show.

Bonus Challenge

Create a calculated field to show the percentage change in sales amount month-over-month for each region.

Show Hint

Use Tableau's LOOKUP() function to compare current month sales with previous month sales.