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!

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

Power Pivot training courses

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

Software ==>	Power Pivot (43 exercises)
Topic ==>	Calculated columns (6 exercises)
Level ==>	Relatively easy
Subject ==>	Power Pivot training

Create DAX calculated columns in Microsoft Power Pivot to generate new values for a list of 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 Calculated Columns.xlsx file in the folder shown above. Open Power Pivot and look at the Building table in the Data view.

Select the empty column at the right of the table and type = to begin creating a new calculated column.

Creating a calculated column in Power Pivot

Begin your formula with an = sign.

Write a formula to add together the values of the Floors above ground and Floors below ground columns and press Enter to complete the calculation.

A DAX calculated column in Microsoft Power Pivot

The entire column will be populated when you enter the formula.

Double-click the column header and change its name to Total floors.

Renaming a calculated column in Power Pivot

Press Enter to finish renaming the column.

Create a new calculated column called Average floor height which divides the Height m column by the Floors above ground column.

You could do this using the division operator:

=Value to Divide / Value to Divide By

Or using the DIVIDE function:

=DIVIDE( Value to Divide, Value to Divide By )

Whichever technique you choose, you should end up with a new column of messy-looking numbers.

A DAX calculated column in Power Pivot producing numbers with many decimal places

Perhaps a few too many decimal places!

Use the formatting tools in the Home tab of the Power Pivot ribbon to display the answers with two decimal places.

Two decimal places should be enough.

Create a pivot table which displays the columns shown in the diagram below:

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

This pivot table is sorted in descending order of the average floor height.

Save and close the workbook.

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:

Courses in Power Pivot

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

Create DAX calculated columns in Microsoft Power Pivot to generate new values for a list of tallest buildings.

Head office

London

Manchester