This page has 0 threads | Add post

Exercises

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

Software ==>	Power BI (160 exercises)
Topic ==>	Calculated columns (12 exercises)
Level ==>	Harder than average
Subject ==>	Power BI training

Add lots of calculated columns to show a matrix summarising 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.

Before you can do this exercise, you'll need to download and unzip this file .

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, 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.