DAXDAX Trainer

DAX Concept

Calculating Building Statistics with Basic DAX Measures

Learn to write fundamental DAX measures using aggregation functions like SUM, AVERAGE, MAX, MIN, and iterator functions like SUMX and AVERAGEX, to calculate various statistics.

BeginnerSUMAVERAGEMAXMINSUMXAVERAGEX

Concept Breakdown

Basic DAX aggregation functions (SUM, AVERAGE, MAX, MIN) operate directly on a column's values. Iterator functions (SUMX, AVERAGEX) are more powerful, allowing calculations to be performed row by row within a specified table before aggregating the results. This enables more complex calculations that involve multiple columns.

What You Will Learn

  1. Understand when to use SUM, AVERAGE, MAX, MIN, SUMX, AVERAGEX 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 'Tallest Buildings - Basic Measures.pbix' file and open it.

  2. Add a new measure called 'Total height m' to the 'Building measures' table using the SUM function on the 'Height m' column.

  3. Add another measure called 'Average height m' to the 'Building measures' table using the AVERAGE function on the 'Height m' column.

  4. Add 'Total height m' and 'Average height m' to the matrix visual and sort it in descending order of 'Average height m'. Format measures for decimal places as needed.

  5. Create a measure called 'Tallest height m' using the MAX function on the 'Height m' column.

  6. Create a measure called 'Shortest height m' using the MIN function on the 'Height m' column.

  7. Create a measure called 'Height range m' by subtracting the 'Shortest height m' measure from the 'Tallest height m' measure.

  8. Add these three new measures to the matrix visual and sort the matrix in descending order of 'Tallest height m'.

  9. Add a measure called 'Average floor height m' which calculates the sum of 'Height m' divided by the sum of 'Floors above ground'.

  10. Add 'Average floor height m' to the matrix and sort the matrix in descending order of this new column.

  11. Add a measure called 'Total floors' using the SUMX function to sum 'Floors above ground' plus 'Floors below ground' for each row.

  12. Use the AVERAGEX function to create a measure called 'Average floors' that calculates the average of 'Floors above ground' plus 'Floors below ground'.

  13. Add 'Total floors' and 'Average floors' to the matrix visual and sort in descending order of 'Average floors'.

Starter DAX

New measure name =

Expected Outcome

A Power BI matrix visual displaying various aggregated and row-context-calculated statistics (total height, average height, tallest, shortest, range, average floor height, total floors, average floors) for buildings, grouped by country and city, sorted according to the instructions.

Checking your sign-in status...