DIVIDE for safe division in Power BI - Dashboard Guide

Dashboard Mode - DIVIDE for safe division

Business Question

How can we calculate the average sales per order safely, avoiding errors when the number of orders is zero?

Sample Data

Region	Sales	Orders
North	1000	50
South	1500	0
East	2000	100
West	1200	60
Central	0	0

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUM(SalesData[Sales])
  Result: 5700
• KPI Card: Total Orders
  Formula: Total Orders = SUM(SalesData[Orders])
  Result: 210
• KPI Card: Average Sales per Order (Safe Division)
  Formula: Average Sales per Order = DIVIDE([Total Sales], [Total Orders], 0)
  Result: 27.14
• Bar Chart: Sales by Region
  X-axis: Region
  Y-axis: Sales
  Shows sales amounts for each region
• Table: Sales and Orders by Region with Average Sales per Order
  Columns: Region, Sales, Orders, Average Sales per Order
  Formula for Average Sales per Order per Region:
  DIVIDE(SUM(SalesData[Sales]), SUM(SalesData[Orders]), 0)
  Shows zero when orders are zero to avoid errors

Dashboard Layout

+----------------------+----------------------+----------------------+  
|   Total Sales (KPI)  |   Total Orders (KPI) | Average Sales/Order  |  
|        5700          |         210          |        27.14         |  
+----------------------+----------------------+----------------------+  
|                            Sales by Region Bar Chart                 |  
|                          (Regions on X, Sales on Y)                  |  
+---------------------------------------------------------------------+  
|          Sales and Orders Table with Average Sales per Order         |  
|  Region | Sales | Orders | Average Sales per Order (safe DIVIDE)       |  
+---------------------------------------------------------------------+  

Interactivity

A slicer filter on Region allows users to select one or more regions. When a region is selected:

• The KPI cards update to show totals and averages only for the selected regions.
• The bar chart updates to display sales for the selected regions.
• The table updates to show sales, orders, and average sales per order for the selected regions.

The DIVIDE function ensures that if any selected region has zero orders, the average sales per order calculation safely returns zero instead of an error.

Self Check

Try this: Add a filter to select only the South region where orders are zero.

Question: What happens to the Average Sales per Order KPI and the table values?

Answer: The average sales per order will show 0 safely without error because DIVIDE handles division by zero by returning the alternate result.

Key Result

Dashboard showing total sales, total orders, and safe average sales per order using DIVIDE to avoid division errors.

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