Category: Hints & Tips

Why use What if?

Have you ever wanted to quickly demonstrate how your data changes in different scenarios? The What if parameter in Power BI is a powerful tool that allows you to do just this, by dynamically transforming your data.

For example, maybe you want to know how much your profits will increase if you raise your price by either 2%, 4% or 6%? Or show the effect of spending more money on marketing? If you know the correlation between price, marketing and profit, you can quickly compare these scenarios and ensure you make the right choice when making critical business decisions.

How do I use What If?

The scenario:

Imagine you want to visualise how the sales total will change if you sell more units. 5%, 10%, or 15% more.

Step 1: Use a matrix to visualise the sales total for each product. Click New Parameter on the Modelling tab, which will open the What-if parameter dialog box. Type a name and data type, in this example the data type will be Decimal number. Type a minimum, maximum and the incremental changes, in this example 0.05 (5%) and click OK.

This will insert a new table, called a parameter table, in your Power BI file and a slicer on your report page.

If you have a look at the new table in Data view, you will see the values.

And if you look at the report page you will see the slicer. Try dragging the handle across and see how the value will change from 0% to 15%.

Step 2: Create a measure you can use to visualise the percentage changes of the quantity.

Start by multiplying the price with the quantity, and then multiply with 1 plus the selected value from the increase quantity table (the parameter table).

Increase quantity =

SUMX (

    tblsales,

    [Quantity] * [Price] * ( 1 + ‘increase quantity'[increase quantity Value] )

)

By adding the measure to the matrix you can now see how it will change the total sales if the quantity increase with 5%, 10%, or 15% – simply drag the slicer across.

Conclusion

Here you have learnt to create a quick and easy What If parameter for dynamically transforming your data in Power BI. Read Part 2 to learn how to personalise your parameter further to give even more powerful insights, or check out our Power BI training page to find out how you can learn even more skills in this amazing reporting tool!

I can use What If analysis, how can I make this even better?

In Part 1 we learnt how to create a simple What if parameter in Power BI. However, you will have more options for analysing your data if you create the parameter tables yourself. This lets you not only work with nonlinear incremental changes (0, 1000, or 1000000), but you can also have more than one column in the table. This is great because in enables you to compare the different scenarios side by side, for even more compelling data analysis.

What is a parameter table?

A Parameter Table is useful when you want to add a slicer and make it change the results of calculations/measures. You achieve this by inserting parameter values into DAX expressions.

It is important when doing this that you define a table that has no relationships with any other tables. Its only scope is to offer a list of possible varieties to the end user so they can modify the calculations performed by one or more calculations/measures.

The DAX expression in calculations/measures uses the value selected and calculates the result accordingly.

How do I use a parameter table in DAX?

In this example, parameter tables are used for next year projections. A matrix shows current year’s profit for the 6 products we are selling. We want the end users to see what next year will bring if the variable price changes.

To create a parameter table click Enter Data on the Home tab in the Data group.

Here the table is called Price Projections with two columns. One column will be used as headers in the visuals, the other with the values in the DAX calculations/measures.

The next step is to create the measure from the values in the Price Projections table. In the DAX below you will see an If is used to test if only one value is selected (the HASONEVALUE function).

On slicers we can do a multiple select, but this will give us a problem, because we only want the slicer to pick up one value from the Price Projections table. The logic in the DAX is that if one value is selected on the slicer, multiply the profit with the value from the Price Projections table, plus 1. If more values are selected on the slicer return the profit only.

Price Projections =

IF (

HASONEVALUE ( ‘Price Projections'[Price Changes] ),

[Profit]

* ( 1 + VALUES ( ‘Price Projections'[Price % values] ) ),

[Profit]

)

In the example below you can see the DAX measure added to the matrix and a slicer created from the field Price Changes from the table Price Projections. Now the matrix will be filtered from the value selected on the slicer.

On the second matrix different scenarios are displayed by using the field Price Changes from the table Price Projections and the DAX measure as value.

Conclusion

Creating insightful scenarios in Power BI can help you make powerful business decisions quickly. At STL we offer a range on Power BI training courses, for complete beginners to advanced users, so get in touch to find out more!