This page has 0 threads | Add post

Exercises

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

Software ==>	Power Pivot (43 exercises)
Topic ==>	KPIs (2 exercises)
Level ==>	Relatively easy
Subject ==>	Power Pivot training

Create KPIs in Power Pivot for Excel to indicate progress towards a target.

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 KPIs.xlsx file in the folder shown above. You'll find a pivot table showing the average height of buildings grouped by country.

An Excel pivot table showing the average height of buildings by Country.

The first few rows of the table.

Create a KPI which compares the Avg Height m measure against the target of the Avg Height m All Buildings measure. Configure the KPI as shown below:

Configuring a KPI in Power Pivot for Excel

Feel free to use different icons.

Add the Status column of the KPI to the pivot table (you may need to remove it and re-add it to make the icons appear).

A KPI icon set displayed in an Excel pivot table based on DAX measures from a Power Pivot data model

You should see something like this.

Create a new measure called Avg Total Floors which calculates the average of the total of floors above and below ground. You can use the AVERAGEX function to do this. Add the measure to the pivot table.

An Excel pivot table showing measures and a KPI from Power Pivot

Some of the results you should see.

Create a new measure called Avg Total Floors All Buildings which removes all filters from the Avg Total Floors measure. Create a KPI for Avg Total Floors using Avg Total Floors All Buildings as a target. Use the settings shown below, picking whichever set of icons you prefer.