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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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?

Cumulative figures

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:

Creating a measure

Create a new measure in the imported data table.

 

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

Cumulative total measure

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:

Table giving running totals

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:

How the formula works

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:

Dog row

The default filter context for a calculation.

 

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

Revised filter context

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:

Filter context for Dog row

The rows considered to get the cumulative total for the Dog row.

 

DAX aficionados will be pleased to hear that you could also solve this problem using the much-harder-to-understand EARLIER function.

This blog has 0 threads Add post