The Power BI running total is the perfect way to display patterns and changes on a specified data over time.
In Power BI, there is a common combination of DAX functions that allow us to create a dynamic cumulative total (sum) on any report page.
The DAX formula that we’re about to discuss is easy to use and provides dynamic results. You can reuse the same formula combination. Just substitute different core measures or core calculations into it. This will enable you to generate cumulative totals (sums) over different calculations from a wide-scale to daily results.
We’ll be using this Quarterly Insights report that I used during the Enterprise DNA Learning Summit last May 2018 as an example.
Using The Date Table And Total Sales In Power BI
When running a cumulative total formula, we need to have a strong date table. This also goes for any time intelligence calculations.
Let’s drag these filters from the Quarterly Insights report to the sample report page. This way, we can drill into any time period.
Then, let’s grab the Date field into the sample report page. This will serve as our date table.
Let’s also add the Total Sales column into the sample report page.
The Total Sales is considered as a simple core measure. This is because it’s easy to calculate. It can also be reused in various ways like Moving Averages or Running Totals.
Now, we need to utilize the correct combination of DAX formulas to get the cumulative total (sum).
Formula For Calculating The Cumulative Revenue
We’ll be using this formula as an example to calculate the Cumulative Revenue for the whole month of July in 2016.
As you can see here, we already have the Cumulative Revenue result that we want. Once we change the context, the cumulative sum also changes. This is what makes it dynamic.
Let’s now try to analyze the given formula.
First, we’ll use the CALCULATE function to change the context of the calculation. Then, we’ll be including the Total Sales measure.
As you can see here, the Total Sales for every single day was displayed.
Using The FILTER Function For Running Totals
Now, we’re going to use the FILTER function. This will adjust the context inside the CALCULATE function.
If we want to display the proper cumulative total, we need to manipulate the current context. This is for us to calculate not just one day, but all the days within that month as well.
As we go down the list, we need to create a wider time frame that we’re currently accumulating.
Every single row within the Cumulative Revenue column is being evaluated to a different context or filter that has been placed over the Sales table.
Using The ALLSELECTED Function For Running Totals
Furthermore, the ALLSELECTED function removes any or all the filters from the Date table that are placed within a certain context. In this case, the context is Q3 of 2016.
Then, it reapplies those filters based on this logic.
To summarize, this part removes all filters over a 3-month window. Then, it iterates through every single one of those days to identify whether that date is less than or equal to the current max date.
Calculating The MAX Date
Let’s try to create a Max Date measure, then assign this logic to it.
This is what the MAX Date logic does.
As you can see, it evaluates to exactly the same day from the Date column.
Different Ways To Use The Power BI Running Total
Once you understand the logic for calculating running totals, it’ll be easier to use it in different ways.
For example, if we want to calculate the Cumulative Profits, we can still use the formula for the Cumulative Revenue. We just need to alter the formula a little bit.
We need to change the name of the measure to Cumulative Profits. Then, change the Total Sales to another core measure which is Total Profits.
Using this formula, we can also get the cumulative revenue of the last quarter. We’ll name this measure Cumulative Revenue LQ.
In this example, we just need to change the Total Sales to a time intelligence calculation like the Sales LQ.
Calculating The Revenue Difference Per Quarter
After having the Cumulative Revenue LQ measure, we can now get the difference between the revenue of the current quarter to that of the last quarter. The term for this technique is Measure Branching.
To do that, we need to create a new measure and name it Revenue Diff per Quarter. Then, we can subtract the Cumulative Revenue LQ from the Cumulative Revenue measure.
After successfully integrating the formula to the previously discussed measures (Cumulative Revenue LQ and Revenue Diff per Quarter), we can now display the visualizations for easier data analysis.
***** Related Links*****
Running Totals In Power BI: How To Calculate Using DAX Formula
Showcasing Budgets In Power BI – DAX Cumulative Totals
Cumulative Totals Based On Monthly Average Results In Power BI
Conclusion
The key point in this tutorial is understanding the formula and then tweaking it further to branch out to other measures.
Using a DAX formula allows you to show trends and provide a concrete comparison of measures over time.
By understanding the function of each section of the formula, you can obtain instantaneous results.
This changes how presentations are done. As long as you’re able to tweak the formula according to the information you require, your desired results will be shown straight away.
This is a good review of the technique for Power BI running total. I hope that you’ll be able to implement this in your own work.
For more DAX formula combination techniques, check out the Solving Analytical Scenarios module at Enterprise DNA Online. This course module covers all formulas that you can use to solve various analysis and insights in your reports.
Enjoy working through this detailed video.
Sam
