Basic arithmetic in DAX in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to see the total profit for each product by subtracting the cost from the sales amount.

📊 Data: You have a sales table with columns: Product, SalesAmount, and Cost.

🎯 Deliverable: Create a report showing each product with its total sales, total cost, and calculated total profit.

Sample Data

Product	SalesAmount	Cost
Chair	150	100
Table	300	200
Lamp	120	80
Sofa	500	350
Desk	250	150

1

Step 1: Load the sales data into Power BI and create a table visual.

Add columns: Product, SalesAmount, Cost to the table visual.

Expected Result

Table shows all products with their sales and cost values.

2

Step 2: Create a new measure to calculate Total Sales by summing SalesAmount.

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

Expected Result

Measure returns the sum of sales amounts for the selected context.

3

Step 3: Create a new measure to calculate Total Cost by summing Cost.

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

Expected Result

Measure returns the sum of costs for the selected context.

4

Step 4: Create a new measure to calculate Total Profit by subtracting Total Cost from Total Sales.

Total Profit = [Total Sales] - [Total Cost]

Expected Result

Measure returns the profit for each product.

5

Step 5: Add the Total Sales, Total Cost, and Total Profit measures to the table visual alongside Product.

Configure table columns: Product, Total Sales, Total Cost, Total Profit.

Expected Result

Table shows each product with its total sales, cost, and calculated profit.

Final Result

Product | Total Sales | Total Cost | Total Profit
----------------------------------------------
Chair   | 150         | 100        | 50
Table   | 300         | 200        | 100
Lamp    | 120         | 80         | 40
Sofa    | 500         | 350        | 150
Desk    | 250         | 150        | 100

✓The Sofa product generates the highest profit of 150.

✓All products have positive profit, meaning sales exceed costs.

✓This simple subtraction measure helps quickly identify profitable products.

Bonus Challenge

Create a new measure that calculates the profit margin percentage for each product.

Show Hint

Use the formula: Profit Margin % = DIVIDE([Total Profit], [Total Sales]) * 100