How do you create accumulative measures on Power BI using DAX? At times your stakeholder wants to see metrics over time in accumulation. For instance, the CRO team (if you don't know what CRO team does, check out basics of digital marketing here) may want to see if conversion rates for experiment variants are stabilizing over time. To observe this, they will want to see accumulative metrics for experiment variants. So let’s start.
Problem
The stakeholder wants to see certain metrics in accumulation in the date range of their choice.
Solution
All you have to do is to create an accumulative measure for each calculation. using CALCULATE, ALLSELECTED, and FILTER. Too busy to read through? Just copy and paste this code and change the highlighted parts:
Measure name = CALCULATE([calculation], FILTER(ALLSELECTED([Date]),[Date] <= MAX([Date])))
Guide with fake dataset
Here’s a fake dataset of experiments data I will use.
Screenshot showing only a part of the dataset:
The only step you need to take - create the accumulative measure for each metric
For the fake dataset, I want to create accumulative measures for: conversion, conversion rate (CVR), Revenue per conversion (RPC), and pageviews per session. We will use conversion to show you how to write the necessary measure and explain what the measure does. The measure you will have to write is:
Accumulative Conversions = CALCULATE(SUM(Experiments[conversion]), FILTER(ALLSELECTED(Experiments[Date]), Experiments[Date] <= MAX(Experiments[Date])))
The code is simple… but can you explain what it’s doing?
This measure calculates the sum of conversion for the entire selected date range as long as the date is equal or less than the currently selected date. This is easier to understand when you look at a table:
As you can see, the measure “Accumulative Conversions” is correctly calculating SUM of conversions of the selected day and before. Look at 6/3/2020 as an example and let’s go back to the measure.
The FILTER part of the measure is what determines the accumulative action:
FILTER(ALLSELECTED(Experiments[Date]), Experiments[Date] <= MAX(Experiments[Date]))
***if you have a calendar table and is connected to the dataset (highly recommended, but not the point of this blog), then use ‘calendar’[date] instead of ‘experiments’[date]
For each row in the table above, Experiments[Date] is selected as a row value. For 06/03/2020, corresponding Experiments[Date] is 06/03/2020. By putting ALLSELECTED(Experiments[Date]), you are making the measure look beyond the currently selected Experiments[Date], which is 06/03/2020. So if you just put ALLSELECTED(Experiments[Date]) and end the measure, it will give you “620” as the conversions for every date.
Why use ALLSELECTED() instead of ALL()?
If you put ALL() instead of ALLSELECTED(), then the measure will look at the entire date range available in the dataset. Therefore, if you have a date range slicer, the measure will not calculate the cumulative sum within the date range but still starting from the minimum of the date range available in the dataset. Here’s an example of this measure using ALL() instead of ALLSELECTED():
In the image above, the left table is using ALLSELECTED() and it starts the cumulative measure on 06/08/2020 due to the date range slicer. In comparison, the right table is using ALL() and despite the date range slicer, the measure is calculating cumulative measure starting on 06/01/2020 (the MIN date of dataset). If you will not use a date range slicer, you can just use ALL() but ALLSELECTED() will give you more flexibility in the future with the option to use a date range slicer.
So now, we need to filter all dates to only select the dates until the currently selected date, which is 06/03/2020. How do we do that? By putting the filter on the date range: Experiments[Date] <= MAX(Experiments[Date]). With ALLSELECTED(Experiments[Date]), you get a list of Experiments[Date] but the currently selected date in that row in the table is still 06/03/2020. By telling the measure to return the dates that are less than MAX(Experiment[Date]), you are putting currently selected date as a set parameter for comparison and filtering the returning date. In case this can help you understand, I created the table below where I put “Accumulative Conversions” as value. In this table, each row shows the conversions for each date and each column and its total shows the accumulative conversions. As you can see, value only shows when column’s date is equal or less than row’s:
the measure, the “MAX” function itself does not matter as much; you can put MIN or AVERAGE and it would still give you the same result because MAX, MIN, and AVERAGE of the date “06/03/2020” is the same. The only difference is the “total” row as you will see the minimum of the total if you put MIN (or the average if you put AVERAGE). Here’s the comparison between MAX and MIN. As I said, total is the only difference:
I recommend using MAX instead of MIN or AVERAGE since, then the total will show the latest (since it will be the biggest — MAX) accumulative number. But for the syntax of the measure, it does not matter.
Conclusion
So now you understand how accumulative measures work. Before I finish, I will also share the measure for Conversion rate, because:
Conversion rate is a calculated metric (conversions/sessions), and
This is what my stakeholder specifically wanted to see. I am a fan of building measures on top of each other.
So I created the following two separate measures to make that happen:
Conversion Rate = SUM(Experiments[conversion])/SUM(experiments[Session])
Accumulative Conversion Rate = CALCULATE([Conversion Rate], FILTER(ALLSELECTED(Experiments[Date]), Experiments[Date] <= MAX(Experiments[Date])))
This will correctly calculate both conversion and session in an accumulative manner and calculate the conversion rate for you, which is what my stakeholder wanted to see whether conversion rate normalizes as time passes.
Bonus:
Since my stakeholders want to see all the metrics in an accumulative manner as well as by date, I will create one visualization with a dynamic accumulative metric selection. If you don’t know how to, go to my previous blog as a reference: How to create a dynamic metric selection for one visualization.
Using the method I wrote about in the blog, you can have one slicer to choose a metric with two visualizations that correspond: one actual, one accumulative. Example screenshot:
Commentaires