Calculated columns vs measures in Power BI - Business Scenario Comparison

Scenario Mode

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

📋 Request: Your manager wants to understand the difference between calculated columns and measures by analyzing sales data. They want you to create examples of both and explain when to use each.

📊 Data: You have a sales table with columns: OrderID, Product, Quantity, UnitPrice, and OrderDate.

🎯 Deliverable: Create a calculated column that shows TotalPrice per row and a measure that calculates Total Sales. Then build a simple report showing both and explain their differences.

Sample Data

OrderID	Product	Quantity	UnitPrice	OrderDate
1001	Notebook	2	5	2024-01-10
1002	Pen	10	1	2024-01-11
1003	Backpack	1	20	2024-01-12
1004	Notebook	3	5	2024-01-13
1005	Pen	5	1	2024-01-14
1006	Backpack	2	20	2024-01-15
1007	Notebook	1	5	2024-01-16
1008	Pen	7	1	2024-01-17

1

Step 1: Create a calculated column named TotalPrice in the sales table that multiplies Quantity by UnitPrice for each row.

TotalPrice = Sales[Quantity] * Sales[UnitPrice]

Expected Result

For OrderID 1001, TotalPrice = 2 * 5 = 10; for OrderID 1002, TotalPrice = 10 * 1 = 10, etc.

2

Step 2: Create a measure named Total Sales that sums the TotalPrice column for all rows in the sales table.

Total Sales = SUM(Sales[TotalPrice])

Expected Result

Total Sales = 10 + 10 + 20 + 15 + 5 + 40 + 5 + 7 = 112

3

Step 3: Build a report with a table visual showing OrderID, Product, Quantity, UnitPrice, and the calculated column TotalPrice.

Add columns: OrderID, Product, Quantity, UnitPrice, TotalPrice to the table visual.

Expected Result

Table shows each order with its TotalPrice calculated per row.

4

Step 4: Add a card visual to the report showing the Total Sales measure.

Add card visual and set value to Total Sales measure.

Expected Result

Card visual displays 112 as the total sales amount.

5

Step 5: Explain the difference: Calculated columns are computed row-by-row and stored in the data model. Measures are calculations done on the fly based on filters and aggregations.

No formula needed; provide explanation in report or notes.

Expected Result

Learner understands calculated columns are for row-level data, measures for aggregated results.

Final Result

Sales Report

OrderID | Product  | Quantity | UnitPrice | TotalPrice
-----------------------------------------------------
1001    | Notebook | 2        | 5         | 10
1002    | Pen      | 10       | 1         | 10
1003    | Backpack | 1        | 20        | 20
1004    | Notebook | 3        | 5         | 15
1005    | Pen      | 5        | 1         | 5
1006    | Backpack | 2        | 20        | 40
1007    | Notebook | 1        | 5         | 5
1008    | Pen      | 7        | 1         | 7

Total Sales: 112

✓Calculated columns create new data for each row and are stored in the table.

✓Measures calculate results dynamically based on filters and aggregations.

✓TotalPrice is a calculated column showing sales per order.

✓Total Sales measure sums all TotalPrice values to show overall sales.

Bonus Challenge

Create a measure that calculates average sales per order and add it to the report.

Show Hint

Use AVERAGEX function on the sales table with the TotalPrice column.