# Cumulative Totals In Power BI Without Any Dates – Advanced DAX

Here, Iâ€™m going to show you how to calculate cumulative totals or running totals with no dates nor index columns. You may watch the full video of this tutorial at the bottom of this blog.

Somebody actually asked about this on the Enterprise DNA forum. The member who posted it encountered some quite complex algorithm running over some information that was required. There was a cumulative or running total requirement but there was no date to it and came across as just a random number generator. But we had to generate a cumulative total from the first result, second result, third result and so on and so forth. Thatâ€™s the reason why I played around with a few ideas to try and find a solution.

Itâ€™s not easy and could get a bit complicated once you go into the formula, but the solution is dynamic and itâ€™s amazing that itâ€™s even possible. Itâ€™s something you can replicate across different environments where you need cumulative totals, or anything of the same nature.

Hereâ€™s the example that weâ€™re going to work with today.

In this case, we have Total Sales where the numbers have been ordered from highest to lowest. Weâ€™re going after a cumulative total, so we want to see the total if rank one is added to rank two, then adding rank three onto that, and so on as you go down the table.

Note that in some cases, you could choose any visualization that works for you. You can, for instance, use a waterfall chart to achieve the results you want.

In our specific example, however, this kind of visualization does not really work.

Now, letâ€™s get to the steps on how to get the cumulative total despite the absence of dates and index columns.

## Creating An Index

I donâ€™t think itâ€™s possible to create a cumulative total from random values. So what you need to do first is to create an index. For this, Iâ€™m going to use the What-If Parameter because this will allow us to easily create dynamic index columns.

So I’m going to call this the Ranking Index. Letâ€™s go with 1 to 200, with an increment of 1. Thereâ€™s no need to add slicers to the page.

This step brings that index into our model. It’s not going to join up via relationship or anything similar. That’s just there to support us with some of the calculations that weâ€™re going to do later on.

## Creating A Table

Now, I’m going to drag this into a table so that we can see the numbers from one onwards.

We actually want to create a result here that shows the values but is not actually a column. So we’re going to return a value within a measure. Weâ€™ll do that by creating another measure and call it Product By Ranking.

This is where the formula gets a little bit more complex, but still very doable.

So weâ€™ll utilize the CALCULATE function and then return with a text value. Then, weâ€™ll go with SELECTEDVALUE, Product Name, and then use more than one value. Next, I’m going to use the VALUES function and FILTER by Product Name.

What weâ€™re trying to go after here is the Ranking Index Value. So I’ll do a RANKX on all products, then go with Total Sales in descending order (because that’s how the initial table was filtered).

Because I’ve actually got a measure created, the Ranking Index Value is then automatically generated from the What-If Parameters.

Letâ€™s bring this into the table. You’ll see that we actually return these results for every single rank now. So we’ve got both the Ranking Index, and now, Product By Ranking.

## Replicating The Data

Now that we have a table showing us which products we have and how theyâ€™re ranked, we want to find out what the Total Sales would be for each particular product. To do that, weâ€™re going to have to replicate the total sales from the old table into the new table.

This time, however, we’re not going to be able to utilize the automatic filter from our data model. We’re going to filter by an actual result in a measure.

To do that, we need to create a new measure which weâ€™ll call Product Sales. So weâ€™re going to calculate the Total Sales, then weâ€™re going to create a different context with new filters. This means our formula will show us filtering the values by Product Name. Then, we’ll go on and create a filter for just a specific Product Name via this Product By Ranking that we just created.

Now, with this filter, if we look at product sixty-three, then weâ€™re going to see the sales for product sixty-three. In other words, weâ€™ve replicated the data while considering specific measures in this section.

## Creating A Cumulative Totals

This time, we are going to create Cumulative Totals based on this Ranking Index. This is really going to show you how much you can utilize the different features and functions of Power BI.

So weâ€™re going to create a new measure and call it Cumulative Product Sales. Just make sure you use the right variables here. For this example, Iâ€™m only going to use one just to keep it simple. Iâ€™ll call it Index Rank and reference the Ranking Index Value for it.

Looking at the formula below, youâ€™ll see that itâ€™s pretty complex. I utilized the SUMX function, did a filter, and then used the SUMMARIZE function on the products.

This will create this dynamic table that will allow us to actually look behind as we go through lines 1, 2, 3, 4, 5 and so on. So if, for example, we want the ranking result below rank 5. Weâ€™ll see that in the table with the sum.

Youâ€™ll also see that what weâ€™re doing here in this particular table is weâ€™re showing the sales of each particular customer. I also want to reference the sales ranking of that customer, so I’ve got to actually put in another RANKX, then go to All Products, then to Total Sales, and then Description.

Now the last thing we need to do here is filter this particular table based on whether the sales ranking is less than or equal to the Index Rank. Once we bring this into our table, you’ll see that this is actually now creating that Cumulative Totals for us.

## Conclusion

Weâ€™ve gone through some key learnings today and itâ€™s really amazing how we were able to create all these new measures.

Basically, we were able to recreate the initial table virtually, allowing us to filter it depending on the data we need. So letâ€™s say you need the sales ranking. Youâ€™ll see that below the index ranking here.

Another thing we did here is that we allowed this table to evaluate every single row or product. Thatâ€™s exactly why this filter works. So we can just look at rows 1 through 5 and get the sales just for that particular table. And then we can expand it down to say 15 rows and then we’re counting up the sales of all of those 15 rows. We’re doing that dynamically as we move down this list, and that’s how we can continuously get a cumulative total.

Note that this solution can also be applied not only to cases where there are no dates nor indexes, but to other similar situations. Hopefully, you can find some unique example where this is required and find a way to utilize it.

## Power BI Workforce Planning & Human Capital Analytics

This Power BI Showcase features comprehensive Power BI workforce planning reports that go in-depth into...

## Extended Date Table Power Query M Function

In today's blog post, we'll take another look at the Power Query M function for the extended date...

## Rule Of Thirds: The Composition Rules

When it comes to presenting the story, we need to start with the layout. This is where the rule of...

## Evaluation Context In DAX Calculations

In this tutorial, we'll learn about evaluation context in DAX. Evaluation or initial context is the...

## Icons In Power BI | DAX, UNICHAR, UNICODE & Custom Images

Icons in Power BI are used to communicate meaning and add more context. You can use them as an...

## How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

When working with calculated data, comparing different data sets will sometimes be necessary. I'm going...

## Heat Map – A Great Visualization For Power BI Reports

In this tutorial, youâ€™ll learn how to create a heat map visual using Charticulator. It is used to...

## CALCULATE Function – How It Can Affect Your Calculations On Power BI

In this tutorial, I want to show you what the CALCULATE function can do through a few examples....

## Remove Empty Columns In Power BI

This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power...

## DAX Calculation Groups – Power BI Report for Problem Of The Week #7

I'm going to talk about the solution I came up with for the 7th Problem of the Week. The problem...

## Publishing PowerApps Applications and Changing The Screen Order

In this tutorial, we're going to talk about publishing PowerApps applications and making sure the...

## Power BI Financial Reporting: Allocating Results To Templates At Every Single Row

Here I want to showcase a unique idea around financial reporting, which is allocating results to...