Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# 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

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...

## Power BI Copilot: Enhancing Data Analysis with AI Integration

Are you ready to elevate your data analysis capabilities? Then let's delve into the realm of Power BI...

## What is Microsoft Fabric? A Guide to Features & Benefits

Imagine a one-stop shop that not only crunches numbers like it's eating cereal for breakfast but also...

## Python AttributeError: ‘dict’ object has no attribute Fix

Python is widely appreciated for its readability and ease of understanding. Among its numerous...

## Power BI vs Tableau: Differences Compared 2023

In the world of data visualization and analytics, two industry-leading data visualization tools stand...