This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	DAX (59 exercises)
Topic ==>	Basic measures (5 exercises)
Level ==>	Relatively easy
Subject ==>	Power BI training

Write basic measures in DAX to calculate statistics for the world's tallest buildings.

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Open the Tallest Buildings - Basic Measures.pbix file in the folder shown above. You should see a matrix visual which uses a measure to count the number of buildings in each country.

A matrix showing the count of buildings in each country in Power BI Desktop.

You can click the + button next to a country to see its cities.

Add a new measure called Total height m to the Building measures table. Use the SUM function to calculate the total of the Height m column.

Adding a measure to a table in Power BI Desktop.

You can right-click the table and choose New measure.

Add a measure called Average height m to the Building measures table. Use the AVERAGE function to calculate the average of the Height m column.

Add the two new measures to the matrix visual and sort it in descending order of Average height m.

A sorted matrix visual in Power BI Desktop.

You can format the measures to control the number of decimal places.

Create three new measures according to table shown below:

Measure name	What it should do
Tallest height m	Use the MAX function to return the biggest Height m.
Shortest height m	Use the MIN function to return the smallest Height m.
Height range m	Subtract the Shortest height m measure from the Tallest height m measure.

Remember that you can reference an existing measure by typing [ and then picking the measure from the list.

Referencing existing measures in DAX for Power BI.

The IntelliSense list appears when you type [

Add these new measures to the matrix visual.

Sort the matrix in descending order of Tallest height m.

Add a measure called Average floor height m which takes the sum of the Height m column and divides it by the sum of the Floors above ground column. Add this new measure to the matrix.

Sort the matrix in descending order of the new column.

Add a measure called Total floors which uses the SUMX function to return the total of the Floors above ground column plus the Floors below ground column. You can see the syntax of the SUMX function below:

SUMX( Table to use, Calculation for each table row )

Use the AVERAGEX function to create a measure called Average floors. This should return the average of the Floors above ground column plus the Floors below ground column.

Add these measures to the matrix visual and sort in descending order of Average floors.

If you're feeling ambitious, you could add an Azure map visual to display the Average floors by Country using a 3D column layer!

A 3D column layer in an Azure map visual in Power BI Desktop.

This is perhaps a little frivolous... but it is fun!

Save and close the report.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post

EXERCISE TOPIC

POWER BI EXERCISES

DAX EXERCISES

Write basic measures in DAX to calculate statistics for the world's tallest buildings.

Head office

London

Manchester