Data source and dataset in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to prepare a report that shows sales performance by product category for the last quarter.

📊 Data: You have access to a sales data source that contains transaction details including Date, Product Category, Product Name, Quantity Sold, and Sales Amount.

🎯 Deliverable: Create a Power BI report that connects to the sales data source, loads the dataset, and displays total sales by product category for the last quarter.

Sample Data

Date	Product Category	Product Name	Quantity Sold	Sales Amount
2024-01-15	Electronics	Smartphone	10	5000
2024-01-20	Electronics	Laptop	5	7000
2024-02-05	Home Appliances	Microwave	8	1600
2024-02-18	Home Appliances	Refrigerator	3	4500
2024-03-10	Furniture	Office Chair	7	1400
2024-03-15	Furniture	Desk	4	1200
2024-03-20	Electronics	Tablet	6	1800
2024-01-25	Furniture	Bookshelf	2	600
2024-02-28	Electronics	Headphones	15	1500
2024-03-30	Home Appliances	Vacuum Cleaner	5	1250

1

Step 1: Connect Power BI Desktop to the sales data source (e.g., Excel file or database) containing the sales transactions.

Use 'Get Data' > select the appropriate source > load the sales data table.

Expected Result

Sales data table is loaded into Power BI with columns: Date, Product Category, Product Name, Quantity Sold, Sales Amount.

2

Step 2: Create a new calculated column to extract the quarter from the Date column.

Quarter = "Q" & FORMAT( 'Sales'[Date], "Q" )

Expected Result

A new column 'Quarter' is added showing values like Q1 for dates in the first quarter.

3

Step 3: Filter the dataset to include only sales from the last quarter (Q1 2024).

Apply a report-level filter where 'Quarter' equals 'Q1'.

Expected Result

Only sales records from January to March 2024 are included in the report.

4

Step 4: Create a measure to calculate total sales amount.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

A measure 'Total Sales' is created that sums the Sales Amount column.

5

Step 5: Build a bar chart visualization with Product Category on the axis and Total Sales as the value.

Visual configuration: Axis = Product Category, Values = Total Sales measure.

Expected Result

Bar chart shows total sales by each product category for Q1 2024.

Final Result

Sales Performance by Product Category (Q1 2024)

| Electronics | ██████████████ 15,800 |
| Home Appliances | ████████ 7,350 |
| Furniture | █████ 3,200 |

(Bar lengths represent total sales amounts)

✓Electronics category has the highest sales in Q1 2024 with $15,800.

✓Home Appliances and Furniture have lower sales compared to Electronics.

✓This report helps focus marketing efforts on top-performing categories.

Bonus Challenge

Add a slicer to the report to allow filtering sales by month within the quarter.

Show Hint

Create a Month column using FORMAT('Sales'[Date], "MMMM") and add it as a slicer visual.