# 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.

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

## How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

When working with calculated data, comparing different data sets will sometimes be necessary. I'm going...

## Heat Map – A Great Visualization For Power BI Reports

In this tutorial, youâ€™ll learn how to create a heat map visual using Charticulator. It is used to...

## CALCULATE Function – How It Can Affect Your Calculations On Power BI

In this tutorial, I want to show you what the CALCULATE function can do through a few examples....

## Remove Empty Columns In Power BI

This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power...

## DAX Calculation Groups – Power BI Report for Problem Of The Week #7

I'm going to talk about the solution I came up with for the 7th Problem of the Week. The problem...

## Publishing PowerApps Applications and Changing The Screen Order

In this tutorial, we're going to talk about publishing PowerApps applications and making sure the...

## Power BI Financial Reporting: Allocating Results To Templates At Every Single Row

Here I want to showcase a unique idea around financial reporting, which is allocating results to...

## Optimizing Queries For A Faster DAX Performance

This tutorial will talk about optimizing your queries in DAX Studio. You'll also learn how to mitigate...

## Format Data In Power BI: Addressing Irregular Data Formats

In today's blog post, we'll discuss Problem of the Week #6. I'll show you how to format data in Power...

## Power BI P&L Statements: Challenges And Solutions

Profit and Loss Statements are often challenging to create in Power BI, especially if you're working...

## How To Install DAX Studio & Tabular Editor In Power BI

In this tutorial, you'll learn how to download and install DAX Studio and Tabular Editor 3 in your...