Advanced Product Budgeting – Actual Results To Budgets Per Product

by Sam McKay, CFA | Business Intelligence, Power BI

I’m going to go over something specific in product budgeting. It’s quite advanced but if you can understand the concepts and the techniques that I go through here, you’ll also have a good understanding of what Power BI can do, how DAX works, and how you can combine a lot of different functions to get great insights. You may watch the full video of this tutorial at the bottom of this blog.

This is from a budgeting workshop that was part of the Enterprise DNA webinar series which can help you if you are working on budgeting, forecasting and benchmarking. But for now, I’m going to focus on just one example.

I’m going to show you how to achieve this table highlighted below.

Download Free Resources Here

It may look relatively simple at a glance, but it’s more complicated than you think. Basically, what we’re trying to do here is we’re trying to evaluate our Total Sales versus our Product Budgets. But there’s a number of nuances on how we create this number because of the way that our model is structured.

If we have the Total Sales and Product Budgets, we should be able to jump to our Actual to Budgets. Then, we can go to Difference to Budget which can give you really good insight. You can throw some conditional formatting over that to showcase what’s actually happening underneath the overall numbers, because it’s breaking it down for us in an effective way.

The problem is, if I jump to the model below, you’ll see that the Dates table actually doesn’t have any relationship with our Product Budgets.

That’s the problem that we’re going to solve here.

Table of Contents

Product Budgets Based On Timeframe

So what we need to do here is dynamically filter our budgets within the formula so that they only show the actual budget across a specific time frame. Otherwise, the Product Budgets are not going to change at all even if we choose a specific timeframe. It’s going to stay as the full amount because there’s no filtering taking place.

We can look at it more closely in this example here.

Now, we’re going to dig deeper into this example by looking at the Budget Allocation formula behind it.

The key thing I want to highlight is if you look at the Budget Allocation, it doesn’t change for the data selection we’re making even though the Total Sales is changing.

And so through the formula, we need to be able to adjust the data based on the date. Now I’ll show you how that’s going to work.

Let me bring the Product Budgets into the table.

The problem here is that there is no relationship between the date and the Product Budget. The only relationship here is between the date and the Total Sales.

So this time, we’re going to look at the formula behind Product Budgets to see how we can build that relationship.

First, we’ll use the SUMX function. Note that by using the SUMX function here, we’re actually building a virtual table. From there, we’re going to utilize the SUMMARIZE function; then we’ll go over the Budgets for every single day.

Now because we have put the virtual table within this particular context and the data involved in it, we can then use ALLSELECTED which in itself is a relatively complicated formula.

By utilizing the ALLSELECTED function, we filter the SUMX virtual table based on the dates. That’s how we can manufacture a relationship based on a filter that doesn’t have a physical relationship to a particular table.

Now, we have this really effective insight that actually showcases the actual results relative to the correct time range.

Conclusion

So the two key things here are the creation of virtual tables, and the use of functions like ALLSELECTED and CALCULATE to filter that table virtually. This is definitely an effective approach you can use from a modelling, report development, and advanced development perspective.

We also saw how using all those functions in a single combination allows us to analyze the data given. Once you get your mind around how these combinations work, you can also begin to understand the many different types of analysis that you can complete inside Power BI.

All the best

Sam

Download Free Resources Here

***** 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