# 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

## Custom Columns In Power BI Vs. Conditional Columns

In this blog post, we’ll talk about the difference between Conditional and Custom Columns in Power BI....

Completing budgeting analysis in Power BI is unfortunately just not that easy. What I’m going to go...

## Matrix Visualization In Power BI

I will show you formatting tricks on how to put think borders on matrix visualizations in Power BI....

## A Best Practice Implementation Strategy for Power BI

In the past, businesses were run based on what you know and who you know.  That is still true...

## Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

## Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

## Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, you’ll learn how to add a sparkline chart in your paginated...

## Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, you’ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...