This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to create running totals or cumulative figures in Power BI Desktop
There are a host of date-based functions in Power BI Desktop for producing cumulative figures, but how do you get running totals for non-date columns? This blog explains!

Posted by Andy Brown on 19 December 2016

In this blog

Creating the measure needed
How and why this works

Non-date running totals in Power BI Desktop using DAX

If you want to create running totals in DAX, you usually use date-based functions like TOTALYTD. However, what happens if your figures aren't calendar-related?

In this example we'll create a DAX measure to calculate the figures in the 4th column.

If you want to try this out yourself, import this Excel file into a new Power BI Desktop model.

I've included the Cumulative column in the Excel workbook as a check - obviously we'll just be recalculating something which is already there!

Creating the measure needed

Rename your imported data as Animals, then right-click on it and choose to create a measure:

Create a new measure in the imported data table.

Type in this measure (or something similar to it):

See under a separate heading below for an explanation of what this does!

You can then include this measure in a table to show the cumulative total:

The table shows the cumulative total imported from the Excel sheet alongside the measure we've just created. Reassuringly, they give the same numbers!

Be careful when creating this table to summarise by the Id or Cumulative columns, otherwise Power BI Desktop will add them together.

How and why this works

So what have we done? Here's the measure again, this time with formatting:

DaxCumulative = CALCULATE(

SUM(Animals[Legs]),

FILTER(

ALL(Animals),

Animals[Id]<>MAX(Animals[Id])

)

Consider the Dog row below:

I've added some formatting to jazz the table up a bit.

The formula works out the total number of legs for the filter context, which would normally be for the animal currently being listed (Dog), giving 4.

However, the CALCULATE function changes the filter context. By default the measure would take this to be the current animal:

The default filter context for a calculation.

However , the measure says "don't use this, but instead use all of the animals":

The FILTER function initially says to use all of the animals, and not just the one for the current filter context.

However, the FILTER function - having removed one constraint - then immediately applies another: namely, that it will only consider animals whose id is less than or equal to the maximum id for the rows for the current filter context (ie 5, in the case of our Dog). So the final filter context is actually this for the Dog row: