This page has 0 threads | Add post

Exercises

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

Software ==>	PowerPivot (72 exercises)
Topic ==>	Measures (2 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Calculate total and average transaction values using measures

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 .

We want to create the following pivot table:

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:

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:

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 a new post