How to Allocate Budgets or Forecasts to Compare to...

EnterpriseDNA · ‎05-31-2020

The budget in your data can be in different granularities. It can be yearly, monthly, quarterly, or weekly, depending on how you define your data. However, you might have sales every day like most businesses. So, you want to break down the numbers, which will give you a like-for-like comparison to your actual sales results.

This is not as easy as it sounds. In fact, this is quite an advanced technique in Power BI. That is why I have brought together my top tutorials about budget allocation in this blog post to help you better understand how this is done inside Power BI.

Power BI allocates your budgets or forecasts based on how you set up your calculations. With the power of DAX formulas, you can manipulate your data and be able to compare the budgets of different granularities versus daily sales information. Doing this in Power BI requires substantial knowledge around structuring the data model, creating correct relationships between the data tables, and using the correct DAX combination. Moreover, you should know how to utilize the correct DAX formulas to make it all work within your report.

The first tutorial that I’ll share with you demonstrates how to compare actual results to yearly budgets. In this tutorial, I bring in some budgeting information into my model to visualize it versus the actual sales results that are happening every day.

This can be achieved by structuring your data model correctly and implementing the correct forecasting technique. Understand how to use an allocation factor to distribute budget values across different time frames.

blog 1.png

This next tutorial is another great example of how to allocate budgets versus daily sales, but here we’re looking at monthly budgets. This is a common scenario when working with forecasts. Check out the video below and see my detailed solution.

The tough part here is to allocate the budget for a month that could also be broken down into various attributes, such as cities, products, etc. This example solution shows a very detailed monthly budget. It’s difficult to solve, but in Power BI it’s definitely possible.

blog 2.png

Now that you have a background in budget analysis techniques, I’ll show you the ultimate budget allocation formula. In this tutorial, I explain in detail the calculation that you can use when dealing with budgets in your own environments.

The formula that I discuss in this tutorial solves the mismatch in granularities of your budget and actual sales. It will allow you to easily change the time contexts (year, month, etc.) with only a few modifications. Once you understand how it works, budget analysis inside Power BI will be a walk in the park for you.

blog 3.png

Key Takeaway

One thing that you need to understand in budget analysis is that your budgets could be at different granularities. It depends on how you set it up in your organization - it could be on a monthly, quarterly, or yearly basis. This creates a mismatch to your sales results because sales are usually at a daily level.

It’s not that easy to implement in Power BI, but I developed some techniques on how to bring in actuals and budgets and see the insights you need visualizing in a compelling way. This is achieved by setting up the data model well, combining DAX, and using the correct formula.

It may take a while to settle in because it’s quite advanced, but once you get a good grasp of how it works, it will be easy for you to implement in your own work. For more information on the subject, check out the links below.

Cheers!

Sam

***** Related Links *****

Showcasing Budgets In Power BI – DAX Cumulative Totals

Managing Seasonality In Your Budget Analytics – Advanced Power BI

Sales Vs Budgets Insights – Extended Budget Allocation Formula

***** Related Course Modules*****

Budgeting & Forecasting

Advanced Data Transformations & Modeling

Mastering DAX Calculations

***** Related Support Forum Posts *****

Allocating Budget - Granularity?