DIVIDE for safe division in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to calculate the average sales per customer for each store, but some stores have zero customers which can cause errors in division.

📊 Data: You have sales data with columns: Store, Total Sales, and Number of Customers.

🎯 Deliverable: Create a report that shows each store's average sales per customer using a safe division method to avoid errors.

Sample Data

Store	Total Sales	Number of Customers
Store A	10000	50
Store B	8000	0
Store C	12000	60
Store D	0	0
Store E	15000	75

1

Step 1: Load the sales data into Power BI and check the columns Store, Total Sales, and Number of Customers.

No formula needed; just import data.

Expected Result

Data table with 5 rows and 3 columns loaded.

2

Step 2: Create a new measure to calculate average sales per customer using safe division.

Average Sales per Customer = DIVIDE(SUM('Sales'[Total Sales]), SUM('Sales'[Number of Customers]), 0)

Expected Result

Measure created that returns average sales per customer or 0 if number of customers is zero.

3

Step 3: Create a table visual in Power BI with Store as rows and the new measure Average Sales per Customer as values.

Table visual configuration: Rows = Store, Values = Average Sales per Customer measure

Expected Result

Table showing each store with average sales per customer, no errors for stores with zero customers.

Final Result

Store     | Average Sales per Customer
--------------------------------------
Store A   | 200
Store B   | 0
Store C   | 200
Store D   | 0
Store E   | 200

✓Stores with zero customers show average sales per customer as 0 without errors.

✓Stores A, C, and E have average sales per customer of 200.

✓Safe division prevents division by zero errors in the report.

Bonus Challenge

Create a conditional formatting rule to highlight stores with average sales per customer below 100 in red.

Show Hint

Use Power BI's conditional formatting on the Average Sales per Customer measure in the table visual.

Practice

1. What is the main purpose of the DIVIDE function in Power BI?

easy

A. To filter rows based on a condition

B. To multiply two numbers quickly

C. To add two columns together

D. To safely perform division and avoid errors when the divisor is zero or missing

Solution

1. Step 1: Understand the problem with normal division

   Dividing by zero or missing values causes errors in Power BI calculations.

2. Step 2: Purpose of DIVIDE function

   DIVIDE handles these cases safely by returning a fallback value instead of an error.

3. Final Answer:

   To safely perform division and avoid errors when the divisor is zero or missing -> Option D

4. Quick Check:

   DIVIDE prevents division errors [OK]

Hint: DIVIDE avoids errors when divisor is zero or blank [OK]

Common Mistakes:

• Thinking DIVIDE multiplies numbers
• Confusing DIVIDE with addition
• Assuming DIVIDE filters data

2. Which of the following is the correct syntax for the DIVIDE function in DAX?

easy

A. DIVIDE(<numerator>, <denominator>, <alternateResult>)

B. DIVIDE(<denominator>, <numerator>, <alternateResult>)

C. DIVIDE(<numerator>, <denominator>) without alternateResult

D. DIVIDE(<numerator> + <denominator>)

Solution

1. Step 1: Recall DIVIDE function parameters

   DIVIDE takes numerator, denominator, and optional alternate result in that order.

2. Step 2: Check each option

   DIVIDE(<numerator>, <denominator>, <alternateResult>) matches correct order and optional third parameter; others have wrong order or syntax.

3. Final Answer:

   DIVIDE(<numerator>, <denominator>, <alternateResult>) -> Option A

4. Quick Check:

   DIVIDE syntax = numerator, denominator, alternate [OK]

Hint: Remember numerator first, then denominator, then optional fallback [OK]

Common Mistakes:

• Swapping numerator and denominator
• Omitting parentheses
• Using plus sign inside DIVIDE

3. Given the DAX measure:

Result = DIVIDE(SUM(Sales[Amount]), SUM(Sales[Quantity]), 0)

What will be the result if SUM(Sales[Quantity]) is zero?

medium

A. An error will occur due to division by zero

B. The result will be blank

C. The result will be 0 (the alternateResult)

D. The result will be the sum of Sales[Amount]

Solution

1. Step 1: Understand the divisor value

   SUM(Sales[Quantity]) is zero, which normally causes division error.

2. Step 2: Effect of alternateResult in DIVIDE

   DIVIDE returns the alternateResult (0) instead of error when divisor is zero.

3. Final Answer:

   The result will be 0 (the alternateResult) -> Option C

4. Quick Check:

   DIVIDE returns fallback value on zero divisor [OK]

Hint: DIVIDE returns fallback if denominator is zero [OK]

Common Mistakes:

• Expecting an error instead of fallback
• Thinking result is blank
• Confusing numerator with denominator

4. You wrote this DAX measure:

SafeRatio = DIVIDE(SUM(Sales[Amount]), SUM(Sales[Quantity]))

But sometimes it returns an error. What is the likely cause and how to fix it?

medium

A. SUM(Sales[Quantity]) is zero; fix by adding alternateResult like 0 in DIVIDE

B. SUM(Sales[Amount]) is zero; fix by changing numerator to 1

C. Syntax error; fix by adding commas between arguments

D. DIVIDE cannot handle sums; use simple division instead

Solution

1. Step 1: Identify cause of error

   Division by zero error happens if denominator SUM(Sales[Quantity]) is zero.

2. Step 2: Fix by adding alternateResult

   Adding a third parameter like 0 to DIVIDE returns fallback instead of error.

3. Final Answer:

   SUM(Sales[Quantity]) is zero; fix by adding alternateResult like 0 in DIVIDE -> Option A

4. Quick Check:

   Add fallback to avoid zero division error [OK]

Hint: Add fallback value in DIVIDE to avoid zero errors [OK]

Common Mistakes:

• Changing numerator instead of denominator
• Ignoring zero divisor issue
• Assuming syntax error without checking logic

5. You want to create a measure that calculates the average price per unit sold but returns "N/A" if quantity sold is zero or missing. Which DAX formula correctly uses DIVIDE to achieve this?

hard

A. AveragePrice = SUM(Sales[TotalPrice]) / SUM(Sales[Quantity])

B. AveragePrice = DIVIDE(SUM(Sales[TotalPrice]), SUM(Sales[Quantity]), "N/A")

C. AveragePrice = DIVIDE(SUM(Sales[Quantity]), SUM(Sales[TotalPrice]), 0)

D. AveragePrice = DIVIDE(SUM(Sales[TotalPrice]), SUM(Sales[Quantity]))

Solution

1. Step 1: Identify numerator and denominator

   Average price = total price divided by quantity sold.

2. Step 2: Use DIVIDE with fallback

   Use DIVIDE with "N/A" as alternateResult to handle zero or missing quantity.

3. Final Answer:

   AveragePrice = DIVIDE(SUM(Sales[TotalPrice]), SUM(Sales[Quantity]), "N/A") -> Option B

4. Quick Check:

   DIVIDE with fallback string for zero divisor [OK]

Hint: Use DIVIDE with fallback string for safe average [OK]

Common Mistakes:

• Swapping numerator and denominator
• Not providing fallback value
• Using simple division without error handling