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 ==>	Average difficulty
Subject ==>	Power Pivot training

Use the ALLEXCEPT function to remove multiple filters in a DAX measure in Power Pivot for Excel.

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 All Except.xlsx file in the folder shown above. You'll find a pivot table displaying a count of shows for each year, quarter and month.

An Excel pivot table from a Power Pivot data model displaying a measure grouped by date

The table has been sorted in descending order.

The aim is to create a measure which calculates the count of shows as a percentage of the year total. One way to do this would involve removing the filters applied by the Month and Quarter fields of the date hierarchy. Here's what the measure might look like:

A DAX measure in Power Pivot using DIVIDE, CALCULATE and REMOVEFILTERS functions

Remove filters from both the month and quarter to return the total for the year.

A different approach would be to remove every filter except for the one applied by the Year field. Create a measure which uses a combination of the DIVIDE, CALCULATE and ALLEXCEPT functions to calculate the count of shows as a percentage of the year total.