Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Showcasing Budgets In Power BI – DAX Cumulative Totals

by | 9:00 am EDT | May 03, 2020 | DAX, Power BI

Recently, I built and showed a Budgeting Analysis Dashboard in one of Enterprise DNA’s workshop. One feature of that Dashboard is the Cumulative Budget view. The webinar is linked here. You may watch the full video of this tutorial at the bottom of this blog.

The dashboard itself is dynamic so I can change the time frame and select the products I want to track. This makes exploring the data extremely efficient if you’re comparing it against a benchmark.

In this tutorial, I’m going to show you how to set up cumulative budgets using DAX. This is one of the more advanced topics of Power BI. It also yields an amazing output for your analysis.

There was a seasonality aspect on my budget data and I needed to display it cumulatively. The visualization I created compares the BUDGET against SALES and SALES LAST YEAR. The dark blue line represents BUDGET and gives a good direction how the performance is against SALES.

In my view, using cumulative totals is the best way to evaluate trends. How your actual results compare versus your budget is ultimately what we want to look at.

## Setting up the Cumulative Totals

We will discuss setting up cumulative totals in detail for this tutorial. We will discuss the formula and technique I used to do it for this dashboard.

First, we need to go to another page to set up the scenario and data table. This makes it easier to see what’s going on with the data itself. We then create a data table with DATE, the TOTAL SALES from the Key Measures and the BUDGET ALLOCATION from the Budget Measures.

The budget is set to allocate for every single day because the data context is by DATE. At the moment, the budget is not cumulative. We are going to use DAX formulas to make it so.

## Dynamic Budget Allocation Formula

The formula looks complicated but if you work out how it’s set up, it will make sense. Variables are used in the formula – I will link the tutorial for the formula in detail.

In your data, the budget can come in different granularities. It can be monthly like in the example above, it can also be yearly or weekly – this depends on how you define your data in the beginning.

Power BI allocates the budget based on how you set up your formulas.

We can switch the DATE field into MONTH & YEAR instead and still get the correct breakdown because our formula is dynamically set up.

## Visualizing the Data

We’ll use bar charts to visualize this data and compare the daily performance to our budget allocation. This already gives us a good insight in itself – however, this is still not cumulative.

Next, we duplicate this chart and turn the duplicate into a table to see the actual values.

## Cumulative Sales and Cumulative Budgets

We’ve talked about Cumulative Sales many times before which follows this formula:

The Cumulative Budgets has a slightly different approach because we need to use complex DAX formulas.

The big difference when calculating Cumulative Budgets is that we can’t use the Budget Allocation by itself. It needs to pass through several DAX formulas to refine it.

If you notice on the right hand side of the SUMX formula we have the Budgets variable. What’s interesting here is we declared the Budgets variable inside the SUMMARIZE formula, DAX formulas can use a column that you virtually created as a reference right away. You will see it is similar with our other Cumulative formulas, except the SUMX portion.

To review, we SUMMARIZE the Budget Allocation at the same time, creating the Budgets variable. We then use SUMX on this Budgets variable to create the virtual table where we get the cumulative totals.

## Visualizing Cumulative Budgets

Now we add the Cumulative Budgets column to the table and we see that it adds the budgets cumulatively on all dates. This is a great way to represent seasonality in your data.

We then remove the columns we don’t need and change the table into a graph. This represents the data effectively in a cumulative way and shows the deviation better.

From the visualization perspective, you will identify trends better by using different elements together. I went through many other samples in the Advanced Budgeting Session. I’ll put a link below to the replay which is up on Youtube as well.

If you want to play around with this sample file, it is up on the Showcase page.

All the best

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

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