What If Analysis with Power BI – What If the UK Leaves the Single Market?

By Gregg Fox, Thu 25 May 2017, in category Business intelligence

Power BI

  
@

Introduction

When it comes to helping our clients make data driven decisions, forecasting and What If analysis are two of our key tools. At the end of 2016, Power BI made forecasting based on a periodic time series easier than ever with the forecasting addition to the Analytics Pane. All of the detail for this feature is both well documented by Microsoft and in numerous blog posts.

What If analysis on the other hand is not so straight forward; at least not yet. Microsoft have notified the community they are planning to add dynamic parameters. While we all eagerly await the monthly Power BI updates, the challenge of providing What If analysis is here and now. So how can we model the effects of different scenarios? In this post, you will taken through the process step by step, but first things first, we need a question to answer.

The Context

All of our customers are facing the uncertainty surrounding the result of the Brexit referendum last year and now that Article 50 has been triggered, we are hearing rumours of companies moving some operations to the mainland but no one knows what the UK’s new relationship with the EU will look like. One of the biggest unknowns and most important results for our clients is whether the UK will remain in the single market and if not, what type of relationship will the UK government be able to negotiate. This will affect large financial institutions and SMEs who export products to Europe.

The Question

As a result of the UK potentially leaving the single market, what effect would potential tariffs have on your business?

The Analysis

Using a retail sales sample data set, we can build What If analysis into Power BI to answer the question above. The report below shows the effect new tariffs will have and allow the user to vary the tariff amounts by product category. We can quickly see where profits take the biggest hit and which products contribute the most to taxable profits. Finally, you can slice and dice your data by shipping method and region to narrow your analysis.

Next Steps

This report only has 3 parameters to control and the parameters are related 1-1 to a product category. This will suffice for a proof of concept What If analysis report but applying potential tariffs at the product subcategory or country level could improve analysis even further. With a more complex data model, the report could answer more complex questions such as "Which products should I manufacture in Europe and where should the site be located?".  If you are starting to see how What If analysis could benefit your business, you can see how the report was built in next week's post.