DAXDAX Trainer

DAX Concept

DAX Exercise: Calculating Average Film Profit Margin with Error Handling

Learn to calculate an average profit margin using DAX, handle potential division by zero errors gracefully, and display multiple related measures effectively in Power BI.

IntermediateDIVIDEAVERAGE

Concept Breakdown

The DIVIDE function in DAX is a robust way to perform division, especially crucial in financial calculations. Unlike the standard '/' operator, DIVIDE allows you to specify an optional 'AlternativeResult' parameter. If the denominator is zero or blank, DIVIDE returns this alternative result (e.g., 0, BLANK(), or any other value) instead of an error (like Infinity or NaN). This prevents your calculations from breaking and ensures your reports remain stable and user-friendly. In this exercise, we'll combine AVERAGEX (or simple AVERAGE depending on context) with DIVIDE to calculate a profit margin based on aggregated data.

What You Will Learn

  1. Understand when to use DIVIDE, AVERAGE in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided Power BI exercise file.

  2. Open the Power BI (.pbix) file.

  3. Create a new table (e.g., named 'Measures') to store all your new measures.

  4. Create a measure called `Average Box Office Takings` to calculate the average box office takings for all films.

  5. Create a measure called `Average Budget` to calculate the average budget for all films.

  6. Now, create a third measure called `Average Profit Margin`. This measure should calculate the profit margin using the formula: `(Average Box Office Takings - Average Budget) / Average Box Office Takings`. Ensure you use the `DIVIDE` function to handle potential division by zero errors, returning 0 in such cases.

  7. Create a table visual and add all three measures (`Average Box Office Takings`, `Average Budget`, `Average Profit Margin`) to it to display your results.

  8. Format the monetary measures to currency and the profit margin as a percentage.

  9. Save your Power BI file as 'Profit margins'.

Starter DAX

Average Box Office Takings = 
    AVERAGE(Films[BoxOfficeTakings])

Average Budget = 
    AVERAGE(Films[Budget])

Expected Outcome

A table visual displaying three measures: 'Average Box Office Takings' (e.g., in millions of dollars), 'Average Budget' (e.g., in millions of dollars), and 'Average Profit Margin' (as a percentage, indicating a positive margin for the listed films), with no errors for films having zero takings.

Checking your sign-in status...