WISE OWL EXERCISES
POWER BI EXERCISES
POWERPIVOT EXERCISES
- PowerPivot data models (7)
- Pivot tables using PowerPivot (2)
- Using Excel tables (3)
- Using other data sources (1)
- Transforming data (Power Query) (7)
- Calculated columns (7)
- Measures (2)
- The CALCULATE function (15)
- More advanced DAX functions (5)
- Calendars (1)
- Date functions (10)
- Hierarchies (2)
- KPIs (5)
- Power View (4)
- Power BI Desktop overview (3)
- Power BI Desktop maps (1)
PowerPivot | Calculated columns exercise | Use IF and SWITCH functions to categorise shopping centres
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.
The answer to the exercise will be included and explained if you attend the course listed below!
Software ==> | PowerPivot (75 exercises) |
Version ==> | Excel 2016 and later |
Topic ==> | Calculated columns (7 exercises) |
Level ==> | Average difficulty |
Course ==> | PowerPivot / Excel Power BI |
- Go into SQL Server Management Studio;
- Open the SQL file you've just unzipped (you can press CTRL + O to do this); then
- Execute this script.
This will generate the database that you'll need to use in order to do this exercise (note that the database and script are only to be used for exercises published on this website, and may not be reused or distributed in any form without the prior written permission of Wise Owl).
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.
Create a new workbook, and import the tblCentre and tblPurchase tables to get a model like this:

Give your tables friendly names, and delete all of the other columns to get a simple model like this.
Use the IF function to create a new calculated column in the Centre table, based on these rules:
Square metres | Category name |
---|---|
More than 100,000 | Violence |
Otherwise | Limbo |
The categories are named after the inner and outer circles of hell in Dante's Inferno (shopping being a hellish activity).
Create a pivot table showing that the bulk of sales took place in limbo:

Most sales took place in smaller shopping centres.
Create another calculated column called Circle in the Centre table using the SWITCH function (you can try a nested IF if you like, but it won't be much fun!). The rules are:
Number of units | Circle |
---|---|
Less than 50 | Limbo |
Less than 100 | Lust |
Less than 150 | Gluttony |
Less than 200 | Greed |
Less than 250 | Wrath |
Less than 300 | Heresy |
Otherwise | Violence |
This should give the following pivot table:

The seven circles of hell, in sales order. Not sure why Dante had two separate categories for greed!
Save this workbook as Lust wins, then close it down.