PowerPivot | Measures exercise | Create Three Measures for Transaction Count / Value

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.

Software ==> PowerPivot  (75 exercises)
Version ==> Excel 2010 and later
Topic ==> Measures  (2 exercises)
Level ==> Average difficulty
Subject ==> Power BI training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

We want to create the following pivot table:

Pivot table summarising by age band

The pivot table shows various statistics for products aimed at Babys and toddlers.

There's a fair bit to do to get this to work!  To start with, if you haven't already done so run the script in the above folder to generate the MAM database (not for commercial use or copying).

Here are the tables/relationships you'll need in your PowerPivot data model:

The four tables

We'll create the columns shown selected in the next stage of the exercise.


To get our pivot table to work, create two calculated columns:

  • A column called Amount in the transactions table (= price times quantity) 
  • A column called PosYear in the Pos table to give the year for each transaction date.

Now create (in this order) the three measures shown below:

The measures needing creation

Create the measures in the order shown in the table below.


The measures to be created are:

Measure  Hints
Number of transactions Count the number of rows in the transactions table.
Total value Sum the Amount column in the transactions table.
Average value  Calculate the total value  / the number of transactions.

You should now be able to create your pivot table, with the average value correctly calculated for each cell! When you've done this, save this workbook as Phew, then close it down.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post