BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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?
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(
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.