Power BI What If Parameters

Good day Databearians and welcome to another exciting data adventure.

We go through life, day in and day out, asking ourselves “What if?”…….Today’s Power BI adventure is just about that. In the latest Power BI update for August 2017, they’ve released this wonderful functionality which enables users to quickly create multiple variables to be used as part of their “what-if analysis”. The possibilities with this are literally endless, giving users and businesses from various different backgrounds the flexibility to design a solution that will give them the answers and insight they so desperately need.

To showcase this functionality we’re going to create a simple demonstration (using the ‘What if Parameters’ of course) to create a UK Property Mortgage Affordability Calculator. While this won’t change the world for most, it will help give users a basic idea of how ‘What if Parameters’ can be used within the Power BI Desktop application.

Let’s get started….to begin let’s identify the variables required for a mortgage affordability calculator:

1. Years: Over what period (in years) will the mortgage be paid?
2. Interest: What % interest (usually offered by the various banks) is likely to be paid over the given period?
3. Deposit: What deposit (if any) will be paid on the property? This will of course lower the amount you will have to pay back to the bank every month.

The most important questions we want to be able to answer from this analysis is:

1. What will my monthly repayment be on the mortgage (given the variables identified) – and can I afford it?
2. What is my total loan value?
3. How much interest will be paid over the duration of the mortgage?
4. What will the total repayment be (including interest)?

I have a simple table called ‘Properties’, in which I manually entered some property listings. For this example I’ll only be using the UKs top Suburbs to live in (according to Google), rather than show actual property addresses (just to be on the safe side). You can of course source real property addresses from a property listing website, which often comes standard with the price, address and lat/long (x, y coordinates) details.

Now that we have data, let’s add some ‘What-if Parameters’ shall we?!

To cover the variables highlighted above, let’s start with years, then add interest and deposit in the same manner.

In the Power BI Desktop (August 2017 Update) Modelling Tab, under the ‘What if’ group, we will now see a button called ‘New Parameter’. Click this button to add your first ‘What If Parameter’.

Fill in the parameter details, using the following steps:

1. Fill in the parameter name. Let’s name our parameter ‘Number of Years’.
2. Choose a data type for your parameter. Options include, whole number, decimal number and fixed decimal number. For our parameter we need our values to be whole numbers.
3. Choose a minimum and maximum value for your parameter. Lets choose a minimum value of 0 and a maximum of 30 years.
4. Choose the value increment. We want to be able to select the number of years in increments of 5 years so we will have options of 5, 10, 15, 20, 25, and 30 years.
5. Choose a ‘Default’ value (if you require one). In our case, let’s assume that most mortgage loans take 20 years to pay off, so we’ll enter 20 as the default value.
6. Check box if you want the parameter to add a slicer for your new ‘What If Parameter’. If left unchecked it will generate the parameter but not add a slicer to your canvas.
7. Click OK to submit and create the parameter.

Ok so now a number of things happened when we submitted the creation of the ‘What If Parameter’, let’s dig deeper to understand.

1. 1. A new table called ‘Number of Years’ were created using a DAX formula called GENERATESERIES()

1. 1. A new measure was created named ‘Number of Years Value’ using a DAX query called SELECTEDVALUE() – which will help us filter our results when we change the ‘Number of Years’ variable with the slicer.

1. On the canvas our ‘What If Parameter’ added a slicer for ‘Number of Years’.

So while you could do all of this manually – the ‘What If Parameters’ did it much quicker and saved us a lot of time, which makes us very happy customers – right?!

One thing to remember is to format the table or measure values to represent the values in the format you need them to be in. For example, if your table values represent a percentage, edit the column values as % Percentage values.

Follow the same process as with ‘Number of Years’ and create the ‘What If Parameters’ for interest and deposit, using the following info:

• Interest: Name the ‘What If Parameter’ ‘Interest Rate %’. Because we are working with percentage, enter the data type as a decimal number, the minimum number as 0.01 (or 1%), the maximum number as 0.15 (or 15%), and the increment as 0.0025 (or 0.25%). Leave default as blank and make sure the check box is on for ‘Add a slicer to this page’.
• Deposit: Name the ‘What If Parameter’ ‘Deposit Amount’. Enter the data type as whole number, the minimum number as 0, the maximum as 1000,000, and the increment as 10,000. Again leave the default as blank and make sure the check box is on for ‘Add slicer to this page’.

Now we have three slicers and four tables, one table for ‘Properties’ (Our data table), one for ‘Number of Years’, one for ‘Interest Rate’ and our final one ‘Deposit Amount’ (Our What If Parameter tables).

To put our UK Property Mortgage Calculator into action we will require the following measures (I’ll assume in this example that you know how to create a measure in Power BI Desktop):

1. Total Price = CALCULATE(SUM(‘Properties'[Property Price])) – is the sum of the property values from our ‘Properties’ table.
2. Number of Payments = [Number of Years Value]*12 – is the value (which will be selected from the ‘Number of Years slicer) multiplied by 12 to give us the total number of monthly payments over the mortgage period. 
3. Monthly Interest Rate = ‘Interest Rate %'[Interest Rate % Value]/12 – is the value (which will be selected from the ‘Interest Rate % slicer) divided by 12 to give us the monthly interest rate % payable over the mortgage period. 
4. Loan Value = ‘Properties'[Total Price]-‘Deposit Amount'[Deposit Amount Value] – This is the total property price minus the deposit value, which will give us the amount loaned from the bank. 
5. Monthly Repayment = ([Loan Value]*[Monthly Interest Rate])/(1-1/POWER((1+[Monthly Interest Rate]),[Number of Payments])) – This calculation is a little bit more complicated, but in essence calculates the Monthly Repayment for the variables we’ve added with our ‘What If Parameters’, i.e. Property Price (From the Property Table), Monthly Interest Rate (derived from our Interest Rate % parameter), and Number of payments (derived from our ‘Number of years’ parameter).  If you are interested in the logic behind this formula, you can go HERE for more details.
6. Total Repayment = [Monthly Repayment]*[Number of Payments] – The monthly repayment value multiplied by the total number of payments over the selected mortgage period. 
7. Total Interest = [Total Repayment]-[Loan Value] – Because we know our total repayment amount, we can just subtract the loan value from the total repayment to give is the Total Interest to be paid.
8. Suburb Name = SELECTEDVALUE(‘Properties'[Suburb],”Multiple Suburbs”) – This measure as used also in the creation of ‘What IF Parameters’ will come in handy with our next measure called ‘Description’ and will in essence return the selected property/suburb name. If no property/suburb is selected, it will return the words “Multiple Suburbs”.
9. Description = “To afford the property at “&[Suburb Name]&”, at a given interest rate of %”&’Interest Rate %'[Interest Rate % Value]*100&” you will need to pay off a monthly mortgage installment of £”&ROUND([Monthly Repayment],0)&” over a period of “&ROUND(‘Number of Years'[Number of Years Value],0)&” years” . I’ve added this last measure, which we will use to show the key metrics in a text sentence, based on the parameters selected from our slicers.

Once all our measures have been added and formatted accordingly, let’s create our UK Property Mortgage Calculator dashboard shall we?

Using the codes within the dashboards, follow the steps below to create your calculator:

• A. Insert a text box from the Home tab / Insert and call the heading ‘UK Property Mortgage Calculator’
• B. As our ‘What If Parameters’ have already been added, just place them accordingly by clicking and dragging into position.
• C. Add the measures, ‘Monthly Repayment’, ‘Loan Value’, ‘Total Interest’, and ‘Total Repayment’ as Card visuals and align / format accordingly.
• D. Add a table visual and place the ‘Suburb’ and ‘Property Price’ values in the table and format the table accordingly.
• E. Add the Power BI Map visual and place the Lat column on ‘Latitude’, the Long column into ‘Longitude’, Suburb into ‘Legend’ and Monthly Repayment into ‘Size’.
• F. Finally let’s add the ‘Description’, measure underneath the heading as a Card visual to finish our affordability calculator.

And there you have it, a fully functional ‘UK Property Mortgage Calculator’. Change the ‘Number of Years’, ‘Interest Rate’, and ‘Deposit Amount’ , select the property you are interested in and voila, the dashboard will answer the questions we identified in the beginning of this blog, here is a reminder:

• What will my monthly repayment be on the mortgage (given the variables identified) – and can I afford it?
• What is my total loan value?
• How much interest will be paid over the duration of the mortgage?
• What will the total repayment be (including interest)?

Notice the dynamic text we entered change based on your selection, giving you a summary of the affordability for each property. It also shows you on the Power BI Map, where the property is situated and the corresponding monthly repayment values in comparison to other properties.

If you would like play with this dashboard, and see how the corresponding changes in parameters affect the values, here is the calculator below:

Thank you for joining us on the journey into the land of mortgages (I know, very exciting), I hope you will find as much value in this new ‘What If Parameter’ functionality as we have.

To learn more about Power BI join our training programs.

Until next time, stay safe, and be blessed!!!