# Effective Techniques for Profit Prediction Using Power BI

What I want to cover with this tutorial are some great examples of profit prediction in the future using Power BI. To achieve this in Power BI you need a number of different modelling and formula techniques, but you must be able to use them correctly and simultaneously. You may watch the full video of this tutorial at the bottom of this blog.

When it comes to the output that you can create inside Power BI, you can predict what might happen in the future by ‘shocking’ some variables around in your calculated logic.

In this tutorial, I’m trying to adjust variables such as how many customers there are as well as the cost of goods sold to those customers. Moreover, I’m attempting to predict what the ultimate outcomes would be if any of these scenarios played out.

What I’ve also enabled in this particular report page is some sensitivity analysis. Not only can we see the end results, but we can also see the variations of results based on the sensitivity inputs that we place inside our reports.

Table of Contents

## Profit Prediction Based on Customer Changes

In my sample dashboard for Profit Predictions report, the first thing that you can see is the table for Customers Change Scenarios. This is the first thing that I made since I want to know how things would be if I could successfully increase the foot traffic into my stores after some marketing efforts.

This table explains how things would play out if I increased my customers by 4 percent (4%), for instance. Also, I want to know how it would affect the underlying trend of my profit in the future.

Basically, I just need to work out the customer forecast first based on the projected foot traffic of customers. Then, I could just increase or decrease the customers using the selection tool.

After that, I need to somehow incorporate this customer increase into the sales data. So I just multiplied the projected customer increase by the average sale per customer that you can see in Avg. Sales Per Customer. From there, I can integrate it to finally show up in the different profit scenarios.

Furthermore, the results for that would give us part of the equation for sales. As you know, Sales subtracted by Cost will give us Profit. So, this part corresponds to the sales part of the equation.

## Profit Prediction Based On Cost Changes

Next, Iâ€™m going to look at the changes to the costs in the Cost Change Scenarios table.

For example, the customers has increased by 7 percent (7%) and the costs per item that we are selling has decreased by 10 percent (10%).

Now, you can automatically see the results for that scenario in the Profit Predictions Based on Customer & Cost Scenarios table over any time frame.

For instance, Iâ€™m going to look at the results for the second and third quarter of 2018. You can see that the initial profit forecast is around \$21 million dollars. And based on the change scenarios that I have selected, it is predicted that the profit will increase to \$26 million. Meaning, there is around \$5 million difference between the profit and an impressive 24 percent (24%) difference in the profit scenario.

In the graph below, you can see the dynamic increase of profit from May 2018 to Sept 2018. You can also see how it changes every single month in this table here.

## Predicting Profitability For Different Regions

Iâ€™ve also created a table for Regional Groups Breakdown and you can see that it has data for South and North Florida.

Now, Iâ€™ll jump to the Quarterly Insights report where you can see a graph here for the Total Sales Per Region.

This is where you can see how divided our sales are based on regions. After seeing the results, you can then research the reasons why there is a big gap between the sales of these regions. It could be because of the management group or it could just be for geographic reasons. Thatâ€™s where the sales team works.

Going back to the Profit Predictions report, if you want to focus more on the data for South Florida, just click South Florida from the Regional Groups Breakdown table. As you can see, there will be a predicted increase in profit of around \$3 million for South Florida.

Meanwhile, for North Florida, there will be more than \$2 million expected increase in profit.

## Creating Sensitivity Analysis

Another thing that I want to go through is creating sensitivity analysis. Instead of clicking through every different scenario here, you can actually create scenario analysis tables. These tables will give you the profit differentials for all the different changes as well as the profit percentage differentials.

Take a look at this example that I set up beforehand.

As you can see, this tutorial is just a preview to a more in-depth event around scenario analysis.Â If you do want to learn more about all of the individual techniques that go into making this work, certainly check out the Scenario Analysis Deep Dive course at Enterprise DNA Online.

## Conclusion

This is some really unique analytical work that you can now do inside Power BI. It just requires utilizing the right features and techniques inside your models and formulas to get this working well.

This prediction type work that you can complete inside Power BI can produce some very impressive insights. So, I highly recommend diving into this one.

Enjoy working through this detailed video.

Sam

[youtube https://www.youtube.com/watch?v=VRzaodb22u4&w=784&h=441]

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Calculate Rolling Totals Using DATESBETWEEN In Power BI

Here Iâ€™m going to show you how you can calculate the amount sold between two different dates using the...

## Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, weâ€™ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...