I want to show here how incredible Power BI is as an analytical tool. I’ll teach you how to develop a **Power BI data model for an advanced scenario analysis**. **You may watch the full video of this tutorial at the bottom of this blog.**

With Power BI, we can achieve so much that we might never have thought possible.

Developing scenario analysis logic within a Power BI data model is probably my favorite technique to use, mainly because it can be so valuable to the business.

Rather than just looking back and reporting on historical information, **what we can do with scenario analysis is almost predict the future**. We can pull levers in our metrics and across our data and see how it impacts the final results. The opportunities become endless!

Almost any data set that explores information over time can use this technique or a form of the techniques that we’ll go through in this tutorial. We’ll also go into **measure branching**, **how to use parameter tables**,** how to insert variables into measures**, and much more.

## Sample Scenario Analysis: Impact Of Price Change

**In this tutorial, we’re going to analyze the impact of price changes on our total revenue.** We’re going to set up our model, shock the prices of particular products, and then see its impact on the entire sales portfolio that we have.

In our data model, we have our Customers, Products, Regions, Dates, and Sales.

In our Sales table, which is a fact table, we have here our Unit Price.

We need to **isolate this Unit price, which gives us our Total Revenue and somehow shock it with a scenario**. For instance, we want to increase our prices by 10% or 20% and we want to see the impact of the price change.

## Total Sales With SUM vs Total Sales With SUMX

First, we look at our Key Measure here called **Total Sales**.

It’s a simple measure that **just sums up the Total Revenue column** **and it’s** **not enough to run this analysis**.Â Total Revenue has already taken into account this Unit Price. So we need to **work out the Total Revenue but use the Order Quantity and the Unit Price**, and we can do that **by using SUMX**.

This formula goes through the **Sales table**. For every row in the Sales table, it multiplies **Order Quantity** by the **Unit Price** for every row. We put this on a table with our Dates and we can see that we get exactly the same result, but it’s only calculating in a different way.

This Total Sales SUMX formula is where we’ll build our scenarios because we can isolate the Unit Price and we can now shock it. The next thing we need is the percentage change in price, which doesn’t exist in our data model, so we have to create it.

## Creating A Supporting Table To Show Price Change Percentage

To create a new table, which will show the price changes, we go **Enter Data** and fill in the columns.

We enter the price changes that we want, such as 2%, 5%, 10%, etc.

Once we load it in, it’s going to create a table for us, but this table has no relationship to our data model.

Then, we make sure to format this into a percentage.

Now that we have this supporting table, we can bring it in and turn it into a slicer.

This enables us to make a selection on how much price change we want to run and make an analysis. Then, we’ll have to create a measure that will harvest the price change percentage being selected in the slicer.

## Harvesting The Price Change Percentage Selected

In this formula (**Pricing Change**/**Pricing Scenario**), which will harvest the price change percentage selected, we’ll use **IF ****HASONEVALUE** with our **Price Change**. Then, with **VALUES**, it will equal to the **Price Change Percent**.Â If it’s not, it’ll be **zero **(0) change.

With this measure, we’re now getting the pricing change based on a selection.

When we select nothing, it shows 0%, so that’s great. No error.

We can now integrate this into our calculations.

We drag this in and we have this dynamic Scenario Sales based on the percentage change.

If nothing’s selected, it’s going to be exactly the same.

## Showing All Sales Impact Of Selected Products

There’s a bit of a problem here, though. When we select three of these products, for example, it impacts everything. All the context has changed and now we’re only seeing the Scenario Sales for just these three products.

In this analysis, we want to see the impact on all our sales based on just the pricing change of certain products. So regardless of any selection made, we want to get the results in our Total Sales that don’t change based on the selection.

To do this, we’ll create another measure and call it **All Sales**. We’ll use the **CALCULATE **function to our **Total Sales**, so we’re still calculating total sales, but we’ll go **ALL Products**.

We drag this into our table and if we select a product, the Total Sales results stay exactly the same, which is shown in the All Sales column.

## Showing Sales Of Selected Products

Furthermore, we want to figure out the **total sales of whatever product or products we select**. In this case, we’ll create a separate calculation just to make sure there’s no issue in our calculations. We can have an additional slicer but it could adjust the calculation. We want to make sure that filters are on or off the correct selections.

This is how we write the measure or formula.

We’re using **CALCULATE Total Sales **once again. Next, go to **ALLSELECTED Products**.Â If we place this inside our table, we’ll see that as we select the product it’s only giving us the sales for that specific selection.

## Working Out The Master Calculation

Now we can create our master calculation to complete our analysis. We’ll use our existing key measures to build this calculation.

So we start with our **All Sales **and we subtract **Selected Sales**, which is the sales with no impact to it. Then we add back the **Scenario Sales**.

If no product is selected, the change in our Selected Scenario Sales is going to be exactly the same as our Scenario Sales.

But if we select some products, the Selected Scenario Sales results are different because the Selected Scenario Sales formula is calculating the entire portfolio by increasing the selected products by the selected percentage.

## Branching Out Into More Measures For More Insights

From this, we can go even further. We can have **Actuals versus Scenarios**, where we **subtract All Sales from Selected Scenario Sales**.

So if we can add this into the table, we can see the additional revenue per day that we would potentially achieve by increasing the price by 10%.

We can also add a different time frame if we only want to look at a specific year, for example.

Moreover, we could look at the **percentage change of our Actuals vs Scenarios**. We can get this by dividing (using the **DIVIDE **function) Actuals vs Scenarios by All Sales, and then we’ll place zero as the alternative result.

If there’s no product selected, it’s going to give the Pricing Scenario selected, which is 10% in this case.

But when we select these products, for example, the results are different because we’re only increasing the price of these three products.

## Creating Compelling Visualizations To Showcase The Results

Now that we’re done with our calculations, we can set up some visualizations to make this look more compelling. We’ll add in some colors to make it more visually appealing.

We can also add some Data labels.

With this visualization set up, we can dynamically shock any of our products by any price change that we have in our slicer.

We might want to add one more card as well, which will give us a summary metric.

So for instance, we want to increase the price by 20% of our top four products, the results in our visualizations will change dynamically as we select the price change and the products.

And that’s how we build a Power BI data model specifically for an advanced scenario analysis.

******* Related Links*********Using Multi-Layered Scenario Analysis in Power BI****Scenario Analysis Techniques Using Multiple ‘What If’ Parameters****Advanced Analytics in Power BI: Layering Multiple ‘What If’ Analysis**

## Conclusion

This is quite advanced but very achievable. It just takes a lot of practice and also really makes you focus on your data. It makes you think hard about the sort of insights you want to test around your future results. Try to identify the variables sitting in your data sets or calculations that you could ‘shock’ or tweak to review their impact on your underlying performance.

I highly recommend this Power BI data model development technique. Be sure to try to incorporate it into your models so you embed the concepts in your mind and take them even further.

Good luck on this one!

Sam

******* Learning Power BI? *********FREE COURSE – Ultimate Beginners Guide To Power BI****FREE COURSE – Ultimate Beginners Guide To DAX****FREE – 60 Page DAX Reference Guide Download****FREE – Power BI Resources****Enterprise DNA Membership****Enterprise DNA Online****Enterprise DNA Events**

Wow, very impressive Sam and just the thing I’m looking to achieve albeit on the cost side but steps should be the same.

Thanks

That’s great. Chrs

Thanks Sam, Great work…. Thanks a lot

No problem. Thanks

Hi Sam

Really enjoying learning from your vids. By far and away you and Ruth are the best learning method that works for me.

Been replicating your YT vid on how to compare accounting periods when DATEADD is unsuitable. However, would really appreciate your thoughts on a method of linking an accounting period into the date table based on a link to a Period Table?

e.g. For my organisation, a five week period for, say, Nov 2017 would range between 30/10/17 – 03/12/17. (The ‘from’ and ‘to’ date would be columns in a ‘Period’ Table.)

Would there be any method you can think of that can link any date (on the date table) to the period table so a ‘Period’ can be returned that I can filter or measure against etc?

Hope this makes sense and any ideas would be appreciated.

Thanks

Steve (UK)

Hi Steve, I would attempt to get those period into the date table by merging them. You want one date table with on the detail in it. Very similar to the example that I run through in the video. I think it becomes to complex it you have other table, when simplifying it shouldn’t be to difficult inside the query editor

Fantastic!

How can I get your sample file? I can’t find it in your website

Check out Enterprise DNA Membership for data downloads that are made available through this.