Power BI Blog: Dynamically Calculating Cumulative Metrics with the ALLSELECTED Function
29 October 2020
Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau looks how to calculate cumulative totals based on a selection on data.
A while ago, I wrote about how to calculate cumulative totals for entire time periods in Power BI here.
That approach calculated a cumulative sum of our total sales amount for the entire dataset. However, what if we want to calculate cumulative totals of selected time periods in the dataset?
Before we do that, lets revisit the measure used to calculate the cumulative total:
Cumulative Sales =
CALCULATE([Total Sales],
FILTER(
ALL(CalendarTable[Dates]),
CalendarTable[Dates] <= MAX(CalendarTable[Dates])
)
)
Let’s have a quick recap. In this measure, the
CalendarTable[Dates] <= MAX(CalendarTable[Dates])
expression delineates the conditions for when to cumulatively add the total sales. The filter context
ALL(CalendarTable[Dates])
enables the measure to look at the entire dataset when calculating. If you wish to read more about this measure you can do so here.
The trick here is to change the filter context in the measure to only what we, the user, have selected, then calculate the cumulative amount. We can do this with the ALLSELECTED function.
Modifying the ‘Cumulative Sales’ measure, by replacing the ALL function with the ALLSELECTED function, we get the following:
Cumulative Sales Sel =
CALCULATE([Total Sales],
FILTER(
ALLSELECTED(CalendarTable[Dates]),
CalendarTable[Dates] <= MAX(CalendarTable[Dates])
)
)
This will tie the filter context for the measure to the selection of dates made by the user. Therefore, each time we change the date selection, the measure will recalculate and return with the cumulative total for the selected date range.
Plotting the measure into a visualisation, we get the following results:
The ‘Cumulative Sales Sel’ measure calculates the cumulative sales from the selection of the date slicer selected.
There we have it, how to calculate the cumulative sum of a metric within a slicer range using the ALLSELECTED function. Do note that both the ALL and ALLSELECTED measures work where we are accumulating based upon a series that may be identified and structured (here, dates). For a more general approach to running totals, please see One Route to a Running Total.
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.
