DAXDAX Trainer

DAX Concept

Creating Fundamental Measures for Film Data Analysis

Learn to create basic DAX aggregate measures like average and count, including the correct application of AVERAGEX when averaging expressions, to analyze film financial data.

BeginnerAVERAGEAVERAGEXCOUNTROWS

Concept Breakdown

This exercise introduces basic DAX aggregation functions. The `AVERAGE` function calculates the arithmetic mean of a column's values. However, when you need to average the result of an expression calculated for each row in a table (e.g., profit per film), the `AVERAGEX` iterator function is essential. `AVERAGEX` evaluates an expression for each row of a specified table and then averages the results. The `COUNTROWS` function simply counts the number of rows in a given table or the number of rows visible in the current filter context.

What You Will Learn

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

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

  3. Complete a beginner-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 file, then open it in Power BI Desktop.

  2. In Power BI Desktop, create a new table to store your measures by going to the 'Modeling' tab and selecting 'New Table'. Name this table 'Film measures'.

  3. Create a new measure within the 'Film measures' table called 'Average Box Office' to calculate the average box office takings, dividing the result by 1,000,000 for readability. Use the formula: `Average Box Office = AVERAGE('Films'[Box Office Takings]) / 1000000`

  4. Create a second measure named 'Average Profit' to calculate the average difference between 'Box Office Takings' and 'Budget' for each film. Since this involves an expression per row, use `AVERAGEX`: `Average Profit = AVERAGEX('Films', 'Films'[Box Office Takings] - 'Films'[Budget])`

  5. Create a third measure called 'Number of films' using the `COUNTROWS` function to count the number of films in the current filter context: `Number of films = COUNTROWS('Films')`

  6. Create a new report page and add a 'Table' visual to it.

  7. Add relevant fields like 'Certificate' and 'Genre' from the 'Films' table, along with your newly created measures ('Average Box Office', 'Average Profit', 'Number of films') to the table visual.

  8. Optionally, sort the table by the 'Average Profit' column in descending order to see top-performing categories.

  9. Save your Power BI file as 'Just let go'.

Starter DAX

My New Measure = // Write your DAX formula here

Expected Outcome

A table visual displaying film certificates and genres, alongside the calculated 'Average Box Office' (in millions), 'Average Profit', and 'Number of films' for each category, allowing for sorting by profit.

Checking your sign-in status...