DIVIDE for safe division in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to safely divide Total Sales by Total Quantity using DIVIDE.

Power BI

Safe Division = DIVIDE([Total Sales], [1])

Drag options to blanks, or click blank then click option'

A[Total Price]

B[Total Quantity]

C[Quantity Sold]

D[Sales Amount]

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column unrelated to quantity as the denominator.

Not using DIVIDE and risking division by zero errors.

The DIVIDE function safely divides Total Sales by Total Quantity, avoiding errors if the denominator is zero or blank.

2fill in blank

medium

Complete the code to divide Revenue by Units Sold safely with DIVIDE and provide 0 as the alternate result.

Power BI

Average Price = DIVIDE([Revenue], [Units Sold], [1])

Drag options to blanks, or click blank then click option'

A1

BBLANK()

C0

DERROR()

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving the alternate result blank, which returns BLANK() by default.

Using ERROR() which causes an error instead of handling it gracefully.

Using 0 as the alternate result ensures the measure returns zero instead of an error when dividing by zero or blank.

3fill in blank

hard

Fix the error in the code by replacing the incorrect division operator with DIVIDE.

Power BI

Profit Margin = [1]([Profit], [Revenue])

Drag options to blanks, or click blank then click option'

ADIVIDE

B/

C*

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using '/' operator which can cause errors if denominator is zero.

Using multiplication or subtraction operators by mistake.

DIVIDE is used for safe division in DAX to avoid errors when dividing by zero or blank values.

4fill in blank

hard

Fill both blanks to create a measure that divides Total Cost by Total Units and returns "No Data" if denominator is zero.

Power BI

Cost per Unit = DIVIDE([1], [2], "No Data")

Drag options to blanks, or click blank then click option'

A[Total Cost]

B[Total Revenue]

C[Total Units]

D[Units Sold]

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping numerator and denominator.

Using unrelated columns causing wrong calculations.

The measure divides Total Cost by Total Units safely and returns the text "No Data" if Total Units is zero or blank.

5fill in blank

hard

Fill all three blanks to create a measure that divides Total Profit by Total Sales, returns 0 if denominator is zero, and formats the result as a percentage.

Power BI

Profit Percentage = FORMAT(DIVIDE([1], [2], [3]), "0.00%")

Drag options to blanks, or click blank then click option'

A[Total Profit]

B[Total Sales]

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 as alternate result which inflates the percentage.

Not formatting the result as a percentage.

This measure safely divides Total Profit by Total Sales, returns 0 if sales is zero, and formats the result as a percentage with two decimals.

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