Changes made in August 2017 to Power BI Desktop
Part two of an eight-part series of blogs

A quiet month, after the frenetic activity of the July update, sees lots of small changes to matrices, tables and charts, but no single big update.

  1. Updates to Power BI Desktop for August 2017
  2. Parameters for What-If Analysis (this blog)
  3. Live Connection to Power BI Service Data Sources
  4. Multiple Values for a Matrix can now Appear as Row Headers
  5. Conditional Formatting of Font Colour
  6. Customising Matrix Subtotals
  7. More Improvement to Line Charts
  8. New Scatter Chart Insights

For a cumulative list of all of the updates to Power BI Desktop in the last few months, see this blog.  Our two-day Power BI Desktop course always uses the latest version of the software.

Posted by Andy Brown on 14 August 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. 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.

Parameters for What-If Analysis

Is Power BI Desktop strictly just a management reporting tool for the past, or should it also be a modelling tool for predicting the future?  Microsoft clearly believe it should be both.

What this feature enables

Suppose that you want to see what future sales will look like (or in this case, what past sales would have looked like) for different currency exchange rates.  Here are a couple of examples - first, at the (current at the time of writing) rate of $1.3 to the pound:

Sales at 1.3 dollars to the pound

Sales at 1.3 dollars to the pound.

Second, what happens if the dollars appreciates - say to $1.1 to the pound?

Sales at 1.1 dollars to the pound

The same chart, but with 1.1 dollars to the pound.

The slicer on the left is based on a what-if parameter, which allows you to choose which of a set of current values you should use in a measure.

Creating the Parameter

Here's how you create a parameter like this:

Creating a parameter

Choose this option on the Modelling tab of the ribbon.

Now complete the dialog box which appears:

What-if parameter

The parameter value will go from 1 to 1.5 in 0.1 increments. By default the parameter will take the value 1.3.

What this will do is to generate a new table of values:

Table of dollar rates

All that this does is to generate a table of values ...

 

Because you've ticked the box, you'll get a slicer too:

Slicer

... and a slicer based on this.

 

For those who have used parameters in Power BI Desktop, this doesn't create an underlying table in Query Editor, and having created the table there's no obvious option for going back to edit your settings.

Creating a measure

To use a what-if parameter, you need some measure to refer to it:

Measure referencing parameter

This measure calculates total sales value multiplied by the current dollar exchange rate.

 

Even though the "parameter" is a table containing multiple values, only one of them can be current at any time.

Putting everything together

You can now chart or report on your measure:

Chart of measure

The chart will update when you choose a different dollar rate.

What-if parameters are thus like a wizard, which generates a table of values that you can choose from. 

This blog has 0 threads Add post