12. What If Parameters

Overview

What If Parameters allow us to adjust the possible values of some important variable in our data model, and see how this affects our visualizations. Learn how to create these parameters in this lesson.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Lesson Goal (00:20)

    The goal of this lesson is to learn how to use a What If parameter in a visualization.

  2. Creating the What If Parameter (00:28)

    A What If parameter is a field in a dataset that can have a variety of possible values. These values are usually adjusted using a slicer. What If parameters can be used when you want to study the impact of different possible values for a field. In our dataset, we use a What If parameter to analyze the effects of changing the percentage of each sale paid to the salesperson as a commission.

    We can create What If parameters by selecting the Modeling tab, and New Parameter. When creating a parameter we specify the data type for the parameter, the minimum and maximum values, and the increment between possible values. We can also choose to add a slicer to our report which can be used to select a value for the parameter.

    After we create the parameter, a new table is added to the model containing two fields. One field contains all the possible values for the parameter, the other contains the currently selected value for the parameter.

  3. Using the Parameter in a Chart (03:32)

    In order to use a What If parameter in a chart, we need to create a measure that uses the currently selected value for the parameter. A measure is simply a formula that we can add to visualizations. In our example, we create a measure that multiplies total revenue by the percentage of revenue not given out in commission. This helps us identify the total revenue after commissions are paid.

    Once we’ve created a measure, we can use it in visualizations like any other field. If we adjust the slicer for our parameter, then the value of the measure will also adjust and the visualization will change accordingly. In our case, we create a bar chart showing the revenue for each month after commission. As we adjust the commission percentage using the slicer, this chart adjusts automatically.

Transcript

In all our lessons so far, we've assumed that the values of our data are definite because our data comes from the past.

However, our data might sometimes be uncertain and we may want to analyze how our data looks for several different potential values. In this lesson, we'll learn how to use a What If parameter in a visualization.

A What If parameter is a field in the dataset that can have a variety of possible values. It can be useful when you want to quickly analyze the impact of various possible scenarios on the business.

In our report, let's create a clustered column chart and add the revenue and date fields.

We want to see revenue by month. So we'll remove year, quarter and day from the date hierarchy.

We now have a simple column chart of revenue by month.

The company's revenue is entirely generated by its salespeople.

When a sale is made, the sales person receives a certain percentage of the sale as a commission.

The company is currently setting the commission percentage for the next year. As part of this process, they want to analyze the impact of various possible commission rates on monthly revenue.

We can do this using a What If parameter.

To create the What If parameter, we'll navigate to the modeling tab and select New Parameter.

In this window, we need to create a name and a data type for the parameter and specify the possible values.

We'll name the parameter commission percentage.

The possible values will be percentages. So we need to set the data type as a decimal number.

We'll allow values of 5% to 30% in increments of 5%.

To do this, we'll set the minimum value as 0.05, the maximum value as 0.3 and the increment as 0.05.

We'll set the default value as 0.1, representing 10%.

We'll keep the option to add a slicer selected and press Okay to create the parameter.

In the fields list, we can see that a new table has been created called commission percentage.

It contains two fields. The first one commission percentage contains the possible values of the parameter while the second one commission percentage value contains the value currently selected from the slicer. We can also see that a slicer has been created, containing the possible values for the parameter.

Before we go on, we'll select the commission percentage field, navigate to the column tools tab and format the field as a percentage.

We'll also remove the decimal places.

The slicer values are now formatted as percentages.

At the moment, our slicer doesn't affect our column chart in any way.

We're going to create a measure of calculating revenue after commission and add it to the column chart.

We'll cover measures in a lot more detail in our decks courses, but for now, all you need to know is that a measure is a formula that we can add to our visualizations.

We'll navigate to the modeling tab, select New Measure, and type the formula.

We'll call the measure revenue without commission.

The formula we'll use the sum formula on our existing revenue then multiply that value by one minus the commission percentage.

We'll then press Enter to create the measure.

We can see that the new measure has been added to our fields list.

We'll drag this measure onto our clustered column chart.

This adds a second bar for each month showing the total revenue after a commission is deducted.

If we use the slicer to change the commission percentage, this new column will adjust.

If we increase the commission rate, we can see the gap between the columns gross.

If we decrease the commission rate, the gap between the columns shrinks.

As we can see the What If parameter makes it easy to analyze the potential revenue values for various different commissions scenarios.

We'll stop the lesson here. In the next lesson, we'll learn how one chart can filter other charts using interactions.

Dashboards and Visualizations
Introduction to Visualizations in Power BI