The Ultimate Budget Allocation Formula For Power BI Analysis

In this tutorial, I’m going to show you the ultimate Budget Allocation formula that you can use when dealing with amounts and data of different granularities. You may watch the full video of this tutorial at the bottom of this blog.

Your Budgets could be at a monthly, quarterly, or yearly granularity while your Sales are usually at a daily level. This creates a mismatch in the model.

The Budget Allocation formula discussed in this tutorial will allow you to solve this mismatch in granularities. It will also enable you to easily switch between different time contexts with just a few adjustments to the algorithm.

After understanding how it works, you’ll be able to effectively utilize this methodology when dealing with budgets in your own environments.

Table of Contents

Mismatched Granularities

Let’s look at the model.

Your Budgets are generally going to be at different granularities. This means that your Budgets might be at a monthly level, for instance Budget per month or per product.

But your Sales or fact table might be at a daily level.

So, there’s a granularity mismatch.

To run an analysis or comparison, you need to find a way to allocate your budgets across the higher granularity.

The higher granularity, in this case, is every day.

So, you need to find a way to take your monthly amounts and distribute them into daily amounts.

Introduction To The Budget Allocation Formula

I call this the Ultimate Budget Allocation formula because you can utilize this technique with Budgets of any granularity. You could use it for quarterly or yearly Budgets. All you need is to do some slight adjustments to make this work.

First, let’s look at the end result of the Budget Allocation formula.

I’ve got my Date in the table, and I can easily change it using the slicer.

I’ve also got my Total Sales amount and Budget Allocation.

The table is referenced in this visualization:

My daily Sales are variable. But my Budgets are constant because they’re monthly amounts.

However, this chart shows the representation better because it’s showing a monthly perspective.

The chart shows the Sales made per month versus the monthly Budget.

Understanding The Allocation Algorithm

Now, let’s look at the formula.

What I want to explain first is this part of the formula:

To understand it, let me give an example. Let’s say there’s a monthly amount. Let’s multiply that amount by 1 divided by 30, or by how many days there are in the month. That becomes the allocation algorithm.

The formula is DayInContext divided by DaysInMonth multiplied by the MonthlyBudgetAmounts. That becomes the methodology which will give you the monthly Budget allocation to every single day.

So, if you had quarterly Budgets, it would be 1 divided by DaysInQuarter multiplied by the Quarterly Budget. That’s how to allocate a quarterly Budget across every single day.

It’s just a matter of building and getting to this point in the formula.

You need to start with this part of the formula in mind. Then, you need to work out how to get the dynamic DayInContext, the DaysInMonth, and how to allocate the MonthlyBudgetAmounts.

In the formula for Budget Allocation, I’ve built it all within VARIABLES because a lot of these individual measures aren’t required.

You should use VAR when a calculation is only relevant to one particular measure.

If a measure can be branched out then, I wouldn’t recommend using VARIABLES. Instead, I would create the initial measure first and then branch out into a Cumulative Total or Moving Average.

Components Of The Budget Allocation Formula

So, let’s now have a look at each individual part of the Budget Allocation formula.

First of all, there’s Days In Context.

This is trying to count up how many dates are in a particular context.

This one’s pretty easy because there’s an evaluation context for every single day. So, if I go COUNTROWS( Dates ), it’s going to give me one day for every single row.

Now, let’s go to Days In Month.

This is an important format because it’s a dynamic way of working out how many days there are in the month.

So, I’m still using COUNTROWS( Dates ), but I’ve changed the context of the CALCULATE function. I removed ALL filters from Dates. But then I return filters using VALUES from the Month & Year.

This gives me a specific Month & Year context. So, for the month of May, it shows 31 days; for June, it shows 30 days.

No matter what month you’re in, it’s going to exactly allocate the entire monthly budget across that month. You won’t get a mismatch where a month has 30 days but then, it’s being divided by 31 days.

So, this enables you to have correct and dynamic allocations.

Using TREATAS For Virtual Relationships

The next thing I want to do is to calculate Total Budget. Now, let’s look at this part of the formula:

The Total Budget formula is a SUM based on a virtual relationship I’ve created.

This virtual relationship is created by the TREATAS function.

In the table, you can see these larger amounts.

These amounts are determined by the selections in the Product Name slicer.

I’m only looking at a select group of products. This is counting up the Total Budget in each of these different months.

Now, let’s look at the Monthly Budget Amounts TREATAS formula.

TREATAS enables me to create a virtual relationship.

I want to create the relationship virtually because there are many month names within the Date table.

And many month names within the Product Name.

Placing a many-to-many relationship creates too many issues in your models.

Instead, I’m going to create a virtual relationship between the Dates table and Product Budgets table in my formula.

The physical relationship between the Dates table and the Sales table is still going to work.

But creating a virtual relationship enables you to compare information between the Dates table and the Sales table by dates.

So, I now have the monthly amounts.

Budget Allocation Example

Let’s go back to the Budget Allocation formula.

Now, I have all the things I need to make it work. There’s Days In Context and Days In Month.

So, by calculating 1 divided by 30 multiplied by the Monthly Budget Amount, I’ll get my daily allocation of 8,778.13.

Going to a different month will give a different daily allocation amount.

Changing Granularity In The Formula

This is the Ultimate Budget Allocation formula because if you had quarterly Budgets, all you need to do is work out how to manipulate this part of the formula for quarterly.

If you had yearly budgets, all you need to do is to allocate it by year. It could be 365 or 90 days. It all depends on the parameter you need to allocate and compare versus your daily results or the results that are at a higher granularity.

Conclusion

This Ultimate Budget Allocation formula allows you to easily create budgeting and finance reports even when dealing with data of different granularities.

With just a few adjustments to the formula, you’ll be able to easily change it to the context you want to show in your reports.

I hope you learned a lot from this tutorial.

All the best,

Sam

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