Power BI tool to simulate scenarios

About me

I work with innovation and industry 4.0 in a multinational for 6 years now. I built the first reports in 2016 and have been working and developing my skills on it ever since, and I'm considered the global reference inside the group for this tool.

The problem I wanted to solve

In the plant I work, in the processing area is the focus. It needs perfect quality, performance and safety levels. For it to work like that and not end up being a bottleneck, it needs a lot of projects and investments each year. This means that the responsible were constantly looking for new projects and trying to build analysis that would become a business case for a project. These analysis were often VERY time consuming. They'd take days for a single analyses and it had to be very well thought from the start. If you wanted to know the exact same thing, including a new equipment, or a different process, that meant you had to start over from the beginning. So I built a tool to help them simulate scenarios to study these business cases.

What is Power BI tool to simulate scenarios?

The Power BI simulator is basically a report where you can view your data. The difference is that you can alter variables of the proccess and see what your data would have looked like if that variable was as the way your project would change.
So let's say you are looking at the sales of your enterprise for last year. And that you are suggesting your enterprise should open stores in a different city, or in different states, where the rent is cheaper and the sale potential is the same.

The "simulator" allows you to change the rent cost for your stores, the way you want and seeing how Profit would change.

Now imagine doing that with every single variable of your process. This means you have a "live" business case.

Tech stack

Let's try to be more straight forward, with some math:
Imagine you have a simple process, let's try to define the productivity:
Process Productivity (%): Machine Uptime [min] / Total proccess time [min]

How do we make it change the way we want?

The process of building Power BI tool to simulate scenarios

First, you will want to create a table for every variable you wish to change in your process. Let's say that you wanna change a variable that would change the Machine Uptime, you wanna buy an equipment that would make maintenance faster and you wanna know how would the Process Productivity change.

So we have to create a table for "Maintenance Time Variation" and input there all the values that I wish to be able to add or remove. Let's say I wanna see what my productivity would look like if I were up to 50 minutes faster every day. And if the equipment is bad, I wanna know if I was AT LEAST 40 or 30 min faster, how would the productivity behave.

The table "Maintenance Time Variation" should look like:
Time Variation
50
40
30
0

And that's it. A four rows table.

Ok, so now we build a slicer out of that column, that will have those same values.

And we re-write our productivity equation to be ->
SumX (Machine Uptime [min] - Maintenance Time Variation) / Total process time [min])

We set the default of the slicer to be 0. When maintenance time variation slicer is 0, your productivity will be the same as the real one. But when your slicer is set to 50, you will see the data of that entire period you are analyzing, as if in every maintenance you had less 50 minutes.

Note: I am assuming in this equation that every entry on the table has maintenance time included for the sake of simplicity.

Challenges I faced

Working** out the math to only later realize how simple it was!

Key learnings

It is especially simple to do this when you start your report already planning for a simulator. That way you build your measures in a way that's simple to integrate with it later.

Tips and advice

Do this for a critical process that is the very priority and has a huge cost reduction potential. Find the money!

Final thoughts and next steps

Making it look pretty by embracing a good design is a plus. It will take your analysis to the next level and people will look at you as someone who has a strong analytical power.