Cumulative Totals In-Depth - The Many Variations I...

Today, I want to talk about cumulative totals. I want to do a deep dive into a few ways that you can create these inside of Power BI.

In this first tutorial, I want to show you the most common pattern which you can utilize to create cumulative totals in a dynamic way.

When I say 'in a dynamic way', what I mean is that you can change the context or date selection in a report page and then your cumulative total always updates to start from the very first date in that particular selection or context.

This is a pretty common and standard way you'll want to calculate cumulative totals and it just requires a reasonable understanding of how CALCULATE works and also how you can incorporate the FILTER function inside of CALCULATE.

In this next example, I run through some time intelligence functions that also calculate cumulative totals. These include MTD, QTD, and YTD calculations.

We don't have to use the generic cumulative total pattern in this case because the calculation is done in a virtual way or it's already pre-programmed to create the cumulative total based on the time intelligence logic that has been built into these functions.

The key is just understanding how to write the functions and showcase them in the right visuals using Power BI.

This last example is a bit more advanced. I show you how you can create a cumulative total without any date. We'll base it on a numeric index or column.

This is quite an interesting topic that is worth diving into because you might want to create in some dynamic way a cumulative total that has no reliance on a date table but rather an index column that you might have in a different table.

I hope you enjoy this tutorial! There is plenty to learn and dive into. Showcasing trends overtime is always best done with cumulative totals. I believe it's a superior way to show trend diversion or any other type of trend analysis.

All the best with this. I suggest you try to incorporate these techniques into your reports and models as soon as you can.

Sam

**** Learning Power BI? ****

FREE COURSE - Ultimate Beginners Guide To Power BI

FREE COURSE - Ultimate Beginners Guide To DAX

FREE - 60 Page DAX Reference Guide Download

FREE - Power BI Resources

Enterprise DNA Membership

Enterprise DNA Online