560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
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!
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.
So what have we done? Here's the measure again, this time with formatting:
DaxCumulative = CALCULATE(
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:
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.
25 Aytoun Street