A couple of posts ago I showed the creation of a data model in Power BI, from start to finished and produces reporting showing expenditure vs budgets for personal bank accounts. This was quite interesting and showed how once the model is created within power BI basic reporting can be produced very fast. But what I reported on was fairly static, you could slice and dice by dimensions but you couldn’t really input any calculations yourself.
One common feature in MI reporting is the ability to ask questions of the data, the most common question is “What If….”. Power BI allows you to do this, and this post will show you how.
Introduction
I want to produce a report which calculates mortgages based on settings selected by a user. I actually don’t need any data to do this – I am simply demonstrating how a user can pass parameters to calculations. So instead of worrying about importing data and model creation. I just need to consider what information I will need off the user. In order to calculate a mortgage I will need to know the following values.
- Mortgage Amount
- Deposit
- Rate of Interest
- Payback Period
Parameters
Power BI allows you to create parameters in the modelling menu.
Once this option is selected a menu appears with some input boxes. Lets create a mortgage amount Parameter by completing the values as below.
What does this actually Do
Upon creation of the parameter you will see a new calculated table containing a calculated column and a measure.
If you inspect these values you can see that 2 different DAX functions are used
- GENERATESERIES
- SELETEDVALUE
Generate Series
Generate series is a function which returns a single column table of values at fixed intervals until an end value is reached. This is what we will use to control the value a user will input (it will be a value within this table). so for example if we use the DAX expression GENERATESERIES(1,5,1) we would have values 1,2,3,4,5
Selected Value
Selected Value returns the specified column where only one value is visible by the current filter context. As we will allow a user to pick only one value on each slider the selected value will be the one value selected from the table generated in the generate series function. Essentially from all the values in the series – the selected value will show the one the user has entered (as that is current in the filter context).
Putting it all together – viewing the parameter on a card.
At this point we have a means of a user selecting a value and us displaying it. We have a measure that Represents the selected mortgage value with the formula
Mortgage Amount Val = SELECTEDVALUE(‘PARAM: Mortgage Amount'[Mortgage Amount], 0)
we can then reference this measure on a visual, notice how if we change the slider the visual updates – as it is referencing the selected value from the series table.
If I then change the slider to 250k.
We repeat this process for the other values until we have the following sliders.
Writing the calculations
we now need to write a few measures to help perform the calculations.
| Name | Description | DAX |
| Amount Borrowed | The amount needed to be borrowed – mortgage amount less deposit | [Mortgage Amount Value] – [Deposit Amount Value] |
| Monthly Payments | The amount to be paid back each month – this is the main loan calculation based on the selected rate of interest and period | var monthlyInterest = DIVIDE([RateOfInterest],12,0) var noOfPayments = [Payback Period (Years) Value]*12 return (PMT(monthlyInterest,noOfPayments,[Amount Borrowed]))*-1 |
| Total Amount to Repay | The total amount to be repaid – amount borrowed + interest over the term | [MonthlyPayments]*12)[Payback Period (Years) Value] |
| Total Interest | The total amount of the repayment about which is interest. | [Total Amount to Repay] – [Amount Borrowed] |
The Report
If I put the measures mentioned above onto card visual I now have a dynamic “What If” report to evaluate the costs of mortgages. In the screenshot below you can see the calculations performed.
and If I increase the rate of interest to 5% (Notice how the totals increase).
Extending the Report
This is very nice and allows me to look at the expense of a mortgage based on my own inputs, but I can already hear you saying – yeah, but I could just do this in excel, and you’d be right. So lets make this report a bit more interesting. Lets create a visual that shows what districts in London we could afford to live in (for an average house) given the settings we use. This would be of great use for an estate agent or mortgage advisor to not only show costs, but what the appropriate mortgage might be able to buy.
Firstly we need some data for Average house prices by London postcode. the following site has this; London property prices by postcode.
We can use the Get Data From Web feature in Excel and save the relevant imported HTML tables as a csv and then import this into Power BI.
As I want to use geolocations on this data it is important I set the postcode field as a postcode.
Once I have this data imported I can use the Map visual. I know that The location field will be the post code I specified. but what will plot the bubbles. I need a way of only plotting them if they are affordable relative to the input from a user. For this we will need to write a a new measures.
AvoidableAreas = IF([AverageHousePrice]<=[Total Capital], [AverageHousePrice],BLANK())
Where total Capital is [Mortgage Amount Val] + [Deposit Amount Value] and AverageHousePrice is the measure representing the Sum(Average Property Price).
So what here is a measure that will look at the input from a user and display the average house price or not if it is less than the total capital input from the user.
so if we have a mortgage value of £100,000 with no deposit;
Nothing is shown. However if we make this £500,000;
We can now see areas in London which are “affordable” (oh the irony!).
Conclusion
This Report shows how user input can be gathered to manipulate scenarios within Power BI. It shows how we can use the input to operate as calculation and then more impressively how we can use those calculations to provide genuine insight via visuals. This example would be a great report for a mortgage advisor and could easily be changed by an estate agent to show properties on their books and what is affordable or not buy a perspective borrower.
Power BI file and data is in GitHub: PowerBI/What If(github.com)