SUM and AVERAGE functions in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the total sales and average sales per transaction for each product category in the last quarter.

📊 Data: You have a sales dataset with columns: TransactionID, ProductCategory, SalesAmount, and TransactionDate.

🎯 Deliverable: Create a Power BI report showing total sales and average sales per transaction by product category for the last quarter.

Sample Data

TransactionID	ProductCategory	SalesAmount	TransactionDate
1001	Electronics	250	2024-01-15
1002	Clothing	80	2024-02-10
1003	Electronics	300	2024-03-05
1004	Home	150	2024-01-20
1005	Clothing	120	2024-03-15
1006	Home	200	2024-02-25
1007	Electronics	400	2024-03-28
1008	Clothing	60	2024-01-30

1

Step 1: Filter the data to include only transactions from the last quarter (January to March 2024).

Use Power BI filter on TransactionDate column: TransactionDate >= DATE(2024,1,1) && TransactionDate <= DATE(2024,3,31)

Expected Result

Filtered data includes all 8 rows as all dates are within Q1 2024.

2

Step 2: Create a measure to calculate total sales by product category.

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

Expected Result

Total Sales sums sales amounts for each product category.

3

Step 3: Create a measure to calculate average sales per transaction by product category.

Average Sales = AVERAGE('Sales'[SalesAmount])

Expected Result

Average Sales calculates the average sales amount per transaction for each product category.

4

Step 4: Build a table visual in Power BI with ProductCategory as rows, and Total Sales and Average Sales as values.

Table visual configuration: Rows = ProductCategory, Values = Total Sales, Average Sales

Expected Result

Table shows total and average sales for Electronics, Clothing, and Home categories.

Final Result

ProductCategory | Total Sales | Average Sales
-------------------------------------------
Electronics     | 950         | 316.67
Clothing        | 260         | 86.67
Home            | 350         | 175.00

✓Electronics has the highest total sales of 950 with an average sale of about 317 per transaction.

✓Clothing has total sales of 260 with an average sale of about 87 per transaction.

✓Home category has total sales of 350 with an average sale of 175 per transaction.

Bonus Challenge

Create a line chart showing total sales trend by month for each product category in the last quarter.

Show Hint

Use the TransactionDate column to group sales by month and ProductCategory for the legend.