# Sales Vs Budgets Insights – Extended Budget Allocation Formula

In this tutorial, I’m going to show you an extended version of the ultimate Budget Allocation methodology that allows you to compare your Sales vs Budgets. You may watch the full video of this tutorial at the bottom of this blog.

The Budget Allocation formula allows you to solve data with mismatched granularities. For example, your Budgets could be at a monthly granularity while your Sales data could be at a daily level.

This is the ultimate Budget Allocation formula:

The formula might look complex, but it’ll be easier to use once you understand how to work through it.

Now, I want to show you how to extend this formula by branching it out into other calculations.

## Showing Difference In Budgets

The table and chart both contain the same data.

In the chart, I’ve overlaid the Total Sales to the Budget.

The Budgets are at a different granularity. For this example, they’re at a monthly granularity while the Sales information is at a daily granularity.

After correctly allocating the Budget, you can start branching out.

For instance, I want to work out the difference in my Budgets. I want to know the Sales versus Budgets.

I’m going to create a new measure and then type in:

All I need to do is get the difference between the Total Sales and the Budget Allocation.

I’ve now branched out into this calculation.

By dragging this new measure into the table, I’ll be able to see the difference in my Budgets.

I can also change this new information into a visualization which I can put under the current chart.

This allows me to make easy comparisons.

## Getting The Cumulative Difference

Another thing you can do is get the Cumulative Total.

This calculation is especially useful in highlighting trends. In this case, I want to show if I’m below the Budget from a cumulative perspective.

Instead of knowing the day-to-day trend, I want to see how this plays out over time. There are a couple of ways of doing this. But I’m going to show this method by throwing in Sales versus Budgets into a Cumulative Total pattern.

However, there are a few nuances to the Cumulative Total pattern. You need to create the two Cumulative Totals separately.

I’ve calculated my Cumulative Totals using this formula:

In the table, you can see that the Cumulative Budget starts at 8,874 and then increases. This is also the trend for the Cumulative Totals.

The Cumulative Totals pattern using Total Sales in its formula is the simpler version.

However, for the Cumulative Budgets formula, I needed to create a virtual table inside SUMX because of the granularity difference.

I needed to iterate through it and then create my Cumulative Budgets.

Now, if I wanted to know my Cumulative Total Difference, all I need to do is calculate the difference between the Cumulative Totals and Cumulative Budgets.

I can then bring the Sales vs Budgets measure into my report. This is then going to show my data cumulatively.

Now, looking at the previous charts, they’re not clear in showing the overall performance.

There are a few spikes above the line for the Budgets. There are also more spikes below it.

These spikes could be because of what happens throughout a week. You may not have high sales throughout this time frame. It could also be because of the Budget that’s allocated evenly across every single day.

But in this particular case, the Cumulative Difference is going to represent the results.

The Cumulative Difference, which was calculated using the Sales versus Budgets formula, shows I’m a bit behind in my Sales.

However, this is not significant.

The charts show that I’m behind by 100,000 for the duration of 11-12 days. But on average, I’m selling about 8,000 to 9,000 every day.

Moving forward, this dynamic calculation will also automatically adjust for any change that you make across your data set.

You can filter with any of the dimensions in your model using slicers.

## Conclusion

This technique is a simple and effective way of branching out from the initial calculations of the Budget Allocation formula.

Hopefully, I was able to simplify this for you. It’s quick and easy to implement. And usually, it’s the simple things that can create very valuable insights in Power BI.

All the best,

Sam

## MultiIndex In Pandas For Multi-level Or Hierarchical Data

MultiIndex in Pandas is a multi-level or hierarchical object that allows you to select more than...

## Power BI DAX ALL Function – How It Works

I want to give you a really quick introduction to the Power BI DAX ALL function. I find that there can...

This blog will show you some of the best tools and tricks to quickly create excellent visuals for your...

## Power BI Percent Of Total – Using CALCULATE Statement

Power BI percent of the total is a really common calculation that we require quite often. This is great...

## Power Query Data Types And Connectors

This tutorial will talk about Power Query data types and connectors. You'll learn what data types are...

## Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power...

## DAX CALCULATETABLE Vs FILTER Function

Both CALCULATETABLE and FILTER are powerful tools for manipulating and analyzing data in DAX query, but...

## Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables

I'll show you how to set up dynamic start dates and end dates using Power BI’s power query date...

## Bookmarks In Power BI – Grouping by Report Page

I'm going to show you how I utilize bookmarks in Power BI. For example, if I have multiple bookmarks...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...

## Creating A Master Reporting Planner For Power BI Deployment

In this tutorial, you’ll learn how to create a Master Reporting Planner in the Analyst Hub to organize...

## Power BI Modulo and Integer-Divide DAX Functions

I thought it’d be interesting to find a way to highlight functions and operators in Power BI that you...