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

Practice

1. What does the DAX expression Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price]) calculate?

easy

A. The division of quantity by price

B. The sum of quantity and price

C. The difference between quantity and price

D. The total sales amount by multiplying quantity and price

Solution

1. Step 1: Understand the multiplication operator in DAX

   The asterisk (*) symbol multiplies two numbers or columns in DAX.

2. Step 2: Analyze the expression components

   SUMX(Sales, Sales[Quantity] * Sales[Price]) iterates over Sales table, multiplies Quantity and Price row-by-row, and sums the results.

3. Final Answer:

   The total sales amount by multiplying quantity and price -> Option D

4. Quick Check:

   Quantity * Price = Total Sales [OK]

Hint: Multiply quantity and price to get total sales [OK]

Common Mistakes:

• Confusing multiplication with addition
• Using division instead of multiplication
• Ignoring the column context in calculation

2. Which of the following is the correct syntax to create a new measure that subtracts Discount from TotalPrice in DAX?

easy

A. NewMeasure = TotalPrice + Discount

B. NewMeasure := TotalPrice - Discount

C. NewMeasure = TotalPrice - Discount

D. NewMeasure - TotalPrice - Discount

Solution

1. Step 1: Recall the correct assignment operator in DAX

   DAX uses the equals sign (=) to assign expressions to measures.

2. Step 2: Check the arithmetic operator for subtraction

   The minus sign (-) subtracts one value from another.

3. Final Answer:

   NewMeasure = TotalPrice - Discount -> Option C

4. Quick Check:

   Use '=' for assignment and '-' for subtraction [OK]

Hint: Use '=' to assign and '-' to subtract in DAX [OK]

Common Mistakes:

• Using ':=' instead of '=' for measure assignment
• Using '+' instead of '-' for subtraction
• Missing the assignment operator

3. Given the DAX measure:
Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])
and the Sales table has Revenue = 1000 and Cost = 700,
what is the value of Profit?

medium

A. 1700

B. 300

C. 700

D. 1000

Solution

1. Step 1: Calculate the sum of Revenue

   SUM(Sales[Revenue]) = 1000 as given.

2. Step 2: Calculate the sum of Cost and subtract

   SUM(Sales[Cost]) = 700, so Profit = 1000 - 700 = 300.

3. Final Answer:

   300 -> Option B

4. Quick Check:

   Revenue - Cost = 300 [OK]

Hint: Subtract total cost from total revenue to get profit [OK]

Common Mistakes:

• Adding instead of subtracting
• Using individual row values instead of sums
• Confusing Revenue and Cost columns

4. Identify the error in this DAX measure:
NetAmount = SUM(Sales[Amount]) + - SUM(Sales[Discount])

medium

A. No error, the syntax is correct

B. Missing parentheses around SUM functions

C. SUM function cannot be used with columns

D. Incorrect use of '+ -' operators together

Solution

1. Step 1: Analyze the operator usage

   The '+ -' uses unary minus after addition, which is valid in DAX.

2. Step 2: Verify the expression

   SUM(Sales[Amount]) + (-SUM(Sales[Discount])) correctly computes net amount as Amount minus Discount.

3. Final Answer:

   No error, the syntax is correct -> Option A

4. Quick Check:

   SUM(Amount) + -SUM(Discount) = Net Amount [OK]

Hint: '+ -' is valid unary minus for subtraction [OK]

Common Mistakes:

• Thinking '+ -' is invalid syntax
• Assuming parentheses are always required around SUM
• Thinking SUM can't be used on columns

5. You want to create a measure that calculates the average price after applying a 10% discount on the Price column. Which DAX expression correctly applies this?

hard

A. AvgDiscountedPrice = AVERAGEX(Sales, Sales[Price] * 0.9)

B. AvgDiscountedPrice = AVERAGE(Sales[Price] * 0.9)

C. AvgDiscountedPrice = SUM(Sales[Price * 0.9]) * 0.9 / COUNT(Sales[Price])

D. AvgDiscountedPrice = SUM(Sales[Price] * 0.9) / COUNT(Sales[Price])

Solution

1. Step 1: Understand the requirement

   We need the average of prices after applying a 10% discount on each price (row context).

2. Step 2: Analyze each option

   Averaging requires row-by-row computation for expressions: AVERAGEX(Sales, Sales[Price] * 0.9) correctly iterates and averages discounted prices. A, C, and D have syntax errors (aggregate on expression without iterator or invalid column reference).

3. Final Answer:

   AvgDiscountedPrice = AVERAGEX(Sales, Sales[Price] * 0.9) -> Option A

4. Quick Check:

   Use AVERAGEX for average of row expressions [OK]

Hint: Apply discount inside AVERAGEX to get correct result [OK]

Common Mistakes:

• Using AVERAGE on expression without iterator
• Using SUM and COUNT instead of AVERAGEX
• Invalid column references like [Price * 0.9]