This page has 0 threads | Add post

Exercises

Many people around the world enjoy and benefit from our exercises. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Software ==>	Power Pivot (43 exercises)
Topic ==>	Basic measures (2 exercises)
Level ==>	Relatively easy
Subject ==>	Power Pivot 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.xlsx file in the folder shown above. You should see a pivot table which uses a measure to count the buildings in each country.

A pivot table in Microsoft Excel based on a Power Pivot data model

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

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

Creating a new Power Pivot measure in Microsoft Excel

You can create a new measure from the Power Pivot tab in the Excel ribbon.

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

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

Power Pivot measures in an Excel pivot tables

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.

Selecting an existing measure from the IntelliSense list in Power Pivot

The IntelliSense list appears when you type [

Add these new measures to the pivot table.

An Excel pivot table displaying DAX measures from Power Pivot

Sort the pivot table 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 pivot table.

A pivot table in Excel showing DAX measures from Power PIvot

Sort the table 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.

DAX measures created in Power Pivot displayed in an Excel pivot table

Add these measures to the table and sort it in descending order of Average floors.

Save and close the file.

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:

Power Pivot learning

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This page has 0 threads

Add a new post

EXERCISE TOPIC

POWER PIVOT EXERCISES

POWER PIVOT EXERCISES

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

Head office

London

Manchester