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 ==>	The CALCULATE function (12 exercises)
Level ==>	Relatively easy
Subject ==>	Power Pivot training

Use the CALCULATE and KEEPFILTERS functions in Power Pivot to alter the behaviour of context filters in Excel pivot tables.

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 Calculate Keep Filters.xlsx file in the folder shown above. You should see a pivot table displaying a list of cities and the results of a measure.

A pivot table in Excel and a Power Pivot DAX measure

Rather than showing only the cities we've specified, the pivot table shows every city with the same value.

Edit the measure so that it returns the correct figure for only the three specified cities. You can wrap the KEEPFILTERS function around the filter in the measure to do this.

A filtered pivot table in Excel using a DAX measure from Power Pivot

The pivot table should change to show the correct results for only the specified cities.

Create a pivot chart and add the City field and the Avg height chosen cities measure to it.

An Excel pivot chart displaying DAX measures from Power Pivot

The chart will filter itself automatically to show only the specified cities.

Create a pivot chart and add the Building Name field to it. Create a measure which shows the sum of an expression which adds the Floors above ground to the Floors below ground field, but only for buildings which have the word tower in their name.

You'll need to combine the CALCULATE, SUMX, KEEPFILTERS and CONTAINSSTRING functions to do this.

Add the measure to the pivot chart to see the results.