A Power BI Training On Budgeting Scenarios Using DAX

So we’re going to really dive into some problem-solving in this Power BI training. This is some really great stuff that you can do with Power BI, but if we just step back for a moment, the key here is, first of all, you’ve got to be able to think analytically. And that’s what I want to do initially. You may watch the full video of this tutorial at the bottom of this blog.

I want to show you how you can look at your data, evaluate it, and then think about how you can take this further. How can we add some more value to this analysis by solving a particular problem that we’re looking at? In this case, we’re looking at our actuals versus budget.

We can look at this and say, well, that’s interesting. We’re slightly behind budget or we’re slightly above budget. But with Power BI, we can take it further and we can say… “how can we actually make budget?”

In this example we set up actual results versus budget. If we’re behind budget we need to ask ourselves what is the difference to our actuals.

Table of Contents

What To Do When You’re Behind Budget

Here is the scenario laid out: you’re making sales all the time and you have set your budgets. But what if you’re behind budget? We want to know what we need to do to get to budget and how many things we need to sell.

This is a great way to start conversations with your sales teams and regional managers. From a marketing perspective, you can give discounts for a particular product or group of products to get your demand. This is the type of analysis that you can see on an almost real-time basis so you’ll know what you need to sell to make up that gap.

I have this page here to show you visually what I mean by tracking versus budget. This is just actuals cumulatively versus budgets cumulatively. There’s quite a bit to setting these up, and there are many measures utilized to arrive at this point. This has been covered in another Power BI training, but I wanted to show you how you can expand and go even further to dive into an analysis that will extract some really good insight.

Evaluating What To Sell To Make Budget

Let’s take a look at this piece of analysis where we can evaluate what we need to sell to make budget. For the first chart, I have the Total Sales and the Budget Allocation for each different city.

The second chart shows what our sales were versus budget so we can quickly see all the negatives, which are the ones that are behind budget obviously. The measure we used to come up with this chart is Total Sales minus Budget Allocation. We need to figure out how much we need to sell in this particular region to make budget.

Now, we have to make a few assumptions here so that it could be extended in a number of different ways. I’ve made some assumptions to simplify things a little bit but what we want to work out is how much we need to sell. If we just think logically, we’ll know how to use all these negative numbers from our Sales vs Budgets chart and divide it by the average sale price of anything sold in these regions. This will get us to where we want.

Product Sales To Reach Budget

So we came up with a measure that says if the Sales vs Budgets is less than 0, then we want to go Sales vs Budgets multiplied by -1 to give it a positive number. Then we will divide it by the average sale price within that particular region. This measure will tell us how many products on average we need to sell in that particular region to get us to budget.

Let’s say for instance this was real data and you saw a cluster of stores that were under budget. This is a quick way to evaluate if we want to even get close to budget for these stores or if we need to go and sell these many things.

What you can do to extend this even more is to look at the average breakdowns of products sold in a particular store and evaluate if you can discount some products to increase demand and get those store sales that we require.

These are the sort of insights that you can get in this Power BI training. And since this is dynamic, you can click through various cities and see which ones are over budgets and which ones are under budgets.

For example, we need to sell 78 additional products in the stores in Auckland to get on budget.

This is a good way to quickly evaluate which cities are over and under budget, and see how that has tracked over time as well.

Conclusion

By combining analytical thinking with analytical capabilities that you have at your disposal with Power BI, we can actually solve these scenarios quite effectively. We can actually showcase what you need to do, and then that will allow you to ultimately take actions that add true value to your organisations.

Using this data model allows you to overlay these results to products or regions quite easily and then reverse engineer what the actual sales are to make it back to our benchmark level.

By utilizing DAX formula step-by-step in this Power BI training, we can achieve this type of insight. I’m hoping you get a lot out of this example and hopefully, you get a lot out of me explaining how I analytically think about things. I think that’s a big part of developing a great model and report in Power BI… you need to get into the zone of thinking well analytically.

It’s not just about dragging this visual in here, or making this look nice using this DAX measure. This is all about what we can see within our data and what we can derive from there. It’s combining all these things which is really, really powerful. Certainly, try and utilize some of these techniques. There’s a lot of value within them, especially if you combine them. So all the best with getting this inside your Power BI models.