Exploring Simple Scenario Analysis in Power BI: Integrating What-If Parameters for Dynamic Insights

[Ilgar_Zarbali]

Since I’m using the same dataset, there’s no need to provide details about it again. You can find all the relevant information about the dataset in my previous articles.

I will now write the following DAX formula using the SUMX function to calculate Total Sales.

Total Sales (S) =
SUMX(
fSales,
fSales[Q-ty]*RELATED(dProducts[Price])
)

To incorporate the What-If Parameters into my scenarios, I will write the following DAX formula.

I will copy the Total Sales (S) measure and rename it to Scenario Sales.

Scenario Sales =
SUMX(
fSales,
fSales[Q-ty]*RELATED(dProducts[Price])
)

Now, let’s create two parameters — Demand Parameter and Price Parameter. I’ll navigate to the Modeling tab and create a new parameter named Pricing Scenarios. The Data Type will be Decimal Number, with the following settings:

• Minimum Value: -0.015
• Maximum Value: 0.35
• Increment: 0.015
• Default Value: 0

I’ll also make sure that the Add slicer to this page option is checked before confirming.

After setting these values, I’ll click the OK button to create the parameter.

Next, I’ll create a Demand Scenarios parameter with the following settings:

• Data Type: Decimal Number
• Minimum Value: -0.05
• Maximum Value: 0.25
• Increment: 0.015
• Default Value: 0

I’ll also make sure that the Add slicer to this page option is checked before confirming.

To display the parameter values as percentages, let’s switch to the Table View. Then, locate the Demand Scenarios column in the Demand Scenarios table and the Pricing Scenarios column in the Pricing Scenarios table. Finally, change their formatting to Percentage with two decimal places.

Next, I’ll add two Card visuals to display the values of my Demand Scenarios and Pricing Scenarios parameters. I’ll also format both values as percentages with two decimal places for better readability.

To observe the impact of the parameters we created, we’ll make some adjustments to the Scenario Sales formula we wrote earlier.

Total Sales (S) =
SUMX(
fSales,
(fSales[Q-ty]*(1+[Demand Scenarios Value]))*(RELATED(dProducts[Price])*(1+[Pricing Scenarios Value])))

Now, from a visualization perspective, let’s drag the StoreName field from the dStore table onto the canvas. Then, add the Total Sales (F) (Current Total Sales) and Total Sales (S) (Scenario Sales) measures to a Column Chart.

We can also enrich our report by adding additional visuals. For instance, we can display current sales by product subcategories to gain a more detailed view of performance.

As a result, the final visual output will look similar to the example shown below.

In our next newsletter, we’ll explore Combining Scenario Analysis with Other DAX Techniques. While preparing this article, I drew upon insights I’ve gained as an expert on the Enterprise DNA platform. https://enterprisedna.co/