WISE OWL EXERCISES
POWER BI EXERCISES
POWER BI EXERCISES
- Basic reports (10)
- Data sources (2)
- Query editor (8)
- Multiple tables (1)
- Filtering data (9)
- Drill-through filtering (1)
- Bookmarks (3)
- Charts (10)
- Other types of visualisation (1)
- Overview of maps (5)
- Calculated columns (7)
- Introduction to DAX (3)
- Creating measures in DAX (1)
- Calendars (2)
- Date functions (2)
- Roles and security (1)
- Advanced data models (4)
- Drill-through and bookmarks (3)
- Custom visuals (3)
- Parameters (5)
- Quick measures (3)
- Report themes (2)
- BI mobile (1)
- Power BI Templates (1)
Power BI | Calculated columns exercise | Use calculated columns to create a data model of house prices
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 one of more of the courses listed below!
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 Power BI report, and load both worksheets from the house prices workbook in the above folder:
Initially the tables aren't joined together. The data show house price sales for the UK for 2015-16, if you're interested!
Use Query Editor to use the first Property types row as a header row, then create a relationship to get something like this:
Better - now we've got something to work with.
Now hide tables and columns and create lots of calculated columns to get your field list looking like this:
See the notes below for what each of these columns should display.
This should enable you to show the following matrix:
Again, see the notes below for what your fields should display.
Here's what the fields should show:
|Field||What is should show|
|Completion year||The year each property sale was completed.|
|Desirability||If the property is either a new build or freehold (F), this should show Desirable; otherwise it should show Not Desirable.|
|Sales band||For properties costing up to £200k, this should show Affordable; for properties between £200k and £500k this should show Not too bad; for properties between £500k and £1,000k this should show Expensive; otherwise this should show Millionaire mansion.|
|Type of property||This should show the description from the Property type table.|
The hardest bit about this is probably getting the sales bands to appear in the correct order in the left-hand column of the matrix.
Save this report as Neat work, then close it down.