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 ==>	Basic measures (2 exercises)
Level ==>	Average difficulty
Subject ==>	Power Pivot training

Create basic DAX measures to calculate statistics about music tours.

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 Music Tours Basic Measures.xlsx file in the folder shown above. You should see a slicer and two pivot tables containing measures.

Pivot tables and a slicer using DAX measures from Power Pivot

You can use the slicer to filter both tables.

The Count Album measure counts the rows of the Album table; the Count Track measure counts the rows of the Track table.

Add a new measure called Avg Album Tracks which divides Count Track by Count Album. Add this measure to the top pivot table.

Pivot tables displaying DAX measures from a Power Pivot data model with a slicer

Your worksheet should now resemble this.

Add a measure which calculates the total track length in seconds. To do this, you'll need to calculate the sum of an expression which multiplies the Track mins field by 60 and adds the Track secs field to it.

Add another measure called Avg Track Length which divides the total track length in seconds by the Count Track measure.

Add these new measures to the pivot tables.

Two Excel pivot tables displaying DAX measures from Power Pivot

Feel free to format the results in any way you like.

Create a new measure which adds the sum of the US sales (m) field in the Album table to the sum of the same field in the Track table. Display this measure in the pivot tables.