Excel and CSV connections in Tableau - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to analyze monthly sales data from two sources: an Excel file with product details and a CSV file with sales transactions. They want a combined report showing total sales per product category.

📊 Data: You have two files: 1) Excel file 'Products.xlsx' with columns ProductID, ProductName, Category. 2) CSV file 'Sales.csv' with columns SaleID, ProductID, SaleDate, Quantity, Price.

🎯 Deliverable: Create a Tableau dashboard that connects to both files, combines the data, and shows total sales amount by product category.

Sample Data

ProductID	ProductName	Category
101	Red T-Shirt	Clothing
102	Blue Jeans	Clothing
103	Running Shoes	Footwear
104	Baseball Cap	Accessories

SaleID	ProductID	SaleDate	Quantity	Price
1	101	2024-01-05	2	15
2	102	2024-01-07	1	40
3	103	2024-01-10	1	60
4	101	2024-01-15	3	15
5	104	2024-01-20	2	10
6	103	2024-01-22	1	60

1

Step 1: Open Tableau and connect to the Excel file 'Products.xlsx'.

In Tableau, click 'Connect', select 'Microsoft Excel', then open 'Products.xlsx'.

Expected Result

Tableau shows the 'Products' table with ProductID, ProductName, and Category columns.

2

Step 2: Add a connection to the CSV file 'Sales.csv' in the same Tableau workbook.

In Tableau, click 'Add' under Connections, select 'Text file', then open 'Sales.csv'.

Expected Result

Tableau shows the 'Sales' table with SaleID, ProductID, SaleDate, Quantity, and Price columns.

3

Step 3: Create a relationship between the 'Products' and 'Sales' tables using the ProductID field.

In Tableau's Data Source tab, drag 'Sales' table next to 'Products' table and create a relationship on 'ProductID'.

Expected Result

Tables are linked by ProductID, enabling combined analysis.

4

Step 4: Create a calculated field 'Sales Amount' in Tableau to calculate total sales per row.

Create calculated field: [Quantity] * [Price]

Expected Result

New field 'Sales Amount' shows total sales value for each sale.

5

Step 5: Build a worksheet showing total sales amount by product category.

Rows: Category; Columns: SUM([Sales Amount])

Expected Result

Bar chart or table showing total sales per category: Clothing, Footwear, Accessories.

6

Step 6: Create a dashboard to display the sales by category visualization with a clear title.

Add the worksheet to a new dashboard; add title 'Total Sales by Product Category'.

Expected Result

Dashboard shows total sales by category clearly for manager review.

Final Result

Dashboard: Total Sales by Product Category

Category     | Total Sales
----------------------------
Clothing     | $115
Footwear     | $120
Accessories  | $20

Bar chart with three bars representing these totals.

✓Clothing category has total sales of $115.

✓Footwear category leads with $120 in sales.

✓Accessories category has the lowest sales at $20.

Bonus Challenge

Add a filter to the dashboard to allow the manager to select sales by month.

Show Hint

Use the SaleDate field to create a date filter and add it to the dashboard for interactive filtering.