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.