Calculated tables in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a summary table that shows total sales and total quantity sold by each product category.

📊 Data: You have a sales transactions table with columns: OrderID, ProductID, Category, Quantity, and SalesAmount.

🎯 Deliverable: Create a calculated table that groups data by Category and shows total sales and total quantity for each category.

Sample Data

OrderID	ProductID	Category	Quantity	SalesAmount
1001	P01	Electronics	2	400
1002	P02	Clothing	5	150
1003	P03	Electronics	1	200
1004	P04	Home	3	300
1005	P05	Clothing	2	60
1006	P06	Home	4	400
1007	P07	Electronics	3	600
1008	P08	Clothing	1	30

1

Step 1: Open Power BI Desktop and load the sales transactions data into a table named 'Sales'.

Load data from source with columns: OrderID, ProductID, Category, Quantity, SalesAmount.

Expected Result

Sales table with 8 rows loaded.

2

Step 2: Create a new calculated table to summarize total sales and quantity by Category.

CategorySummary = SUMMARIZE(Sales, Sales[Category], "Total Quantity", SUM(Sales[Quantity]), "Total Sales", SUM(Sales[SalesAmount]))

Expected Result

A new table 'CategorySummary' with 3 rows, one for each category: Electronics, Clothing, Home.

3

Step 3: Verify the calculated table values by checking totals for each category.

Check sums: Electronics Quantity=6, Sales=1200; Clothing Quantity=8, Sales=240; Home Quantity=7, Sales=700.

Expected Result

CategorySummary table shows correct totals matching the sales data.

4

Step 4: Create a simple table visual in Power BI report using the 'CategorySummary' table.

Add Table visual with fields: Category, Total Quantity, Total Sales from CategorySummary.

Expected Result

Table visual displays categories with their total quantity and sales.

Final Result

CategorySummary Table
+-------------+---------------+------------+
| Category    | Total Quantity| Total Sales|
+-------------+---------------+------------+
| Electronics | 6             | 1200       |
| Clothing    | 8             | 240        |
| Home        | 7             | 700        |
+-------------+---------------+------------+

✓Electronics category has the highest total sales of 1200.

✓Clothing category sold the highest quantity of 8 units.

✓Home category sales and quantity are moderate compared to others.

Bonus Challenge

Create a calculated table that shows the average sales amount per order for each category.

Show Hint

Use AVERAGEX function on grouped data or divide total sales by count of orders per category.