Power BI - Cumulative (Running) Total - Measure or Calculated Column?

Showing Cumulative or Running Totals is not an uncommon requirement in reporting, hence it is required with almost all reporting solutions. I wrote two posts on this: Analysis Services (SSAS): Calculating running totals for a given period using MDX and SQL Server: Calculating running totals using T-SQL that show multiple ways of calculating cumulative totals discussing the ways of getting cumulative totals using MDX and TSQL. Now it is time to write the same using DAX. DAX is the language used for writing expressions with both Analysis Services Tabular Model and Power BI and it is a rich and easy-to-learn language.

Should I create a Measure or Calculated Column for Cumulative Totals?

Cumulative Totals can be implemented either using Calculated Columns or Measures. It is up to you to decide because the result may vary based on the way they are visualized. Once decided, you need to understand the context related to the functions used for the calculation because the behavior of the functions are different.

Generally, if I have to show the Cumulative Totals with all detail lines as well as Totals, then it is better to implement it as a Measure because the calculation does not happen as we expect at Totals level. Example, if you need to show rows in a Table Visual, and you want to see Cumulative Totals for each line as well as in Totals of the Table Visual, then Measure is the best choice. If not, Calculated Column will do the needful.

The calculation for the Cumulative Totals using Calculated Column and Measure is different. Because of that, you cannot use the same expression for the calculation.

Again the calculation is different if the source table has multiple columns. If there are multiple columns, they need to be included in filtering for calculating the correct value.

Creating Cumulative Totals

Let's take two tables and see how we can generate the Cumulative Totals. Let's use two tables shown below;

As you see, first table ItemsCompletedByDayAndTeam contains values for days and teams. The second table ItemsCompletedByDay contains values only for days.

Let's see how we can add Cumulative Totals using Calculated Columns. Usually we use the following expression for calculating it but It does not produce the totals we need, it shows the total of ItemCompleted in all rows.

CALCULATE
(
   SUM(Table[ValueColumn])
   , FILTER
     (
        ALL(Table[KeyColumn])
 , Table[KeyColumn]  <= MAX(Table[KeyColumn])  
     )
)

The reason for this is, MAX Function used. Since the context becomes the entire table for functions when they are used with Calculated Columns, it returns the max-value considering all values. What we need is the current value for the expression written, hence EARLIER Function should be used instead.

Here are the two expressions against two tables;

-- Expression against the ItemsCompletedByDayAndTeam table
CL_CT_1 = CALCULATE
          (
             SUM(ItemsCompletedByDayAndTeam[ItemsCompleted])
             , FILTER
               (
                  ALLEXCEPT(ItemsCompletedByDayAndTeam, ItemsCompletedByDayAndTeam[Team])
                  , ItemsCompletedByDayAndTeam[Day]  <= EARLIER(ItemsCompletedByDayAndTeam[Day]) 
                      && ItemsCompletedByDayAndTeam[Team] <= EARLIER(ItemsCompletedByDayAndTeam[Team])  
               )
          )

-- Expression against the ItemsCompletedByDay table
CL_CT_2 = CALCULATE
          (
              SUM(ItemsCompletedByDay[ItemsCompleted])
              , FILTER
                (
                   ALL(ItemsCompletedByDay[Day])
                   , ItemsCompletedByDay[Day]  <= EARLIER(ItemsCompletedByDay[Day])  
                )
          )

If we use these two with two Table Visuals, this is how it looks like;

Notice the values in columns and totals of them. Expressions we have written work well with each row but not with totals. If you do not need totals for Cumulative Totals, you can use Calculated Columns for calculating Cumulative Totals.

Although the calculation is possible with Calculated Columns, it is always better to use Measures instead. If a measure is used, then we do not need to worry on totals as it shows them accurately.

Here are the expressions for measures against both tables;

-- Expression against the ItemsCompletedByDayAndTeam table
M_CT_1 = CALCULATE
         (
            SUM(ItemsCompletedByDayAndTeam[ItemsCompleted])
            , FILTER
              (
                 ALL(ItemsCompletedByDayAndTeam)
                 , ItemsCompletedByDayAndTeam[Day] <= MAX(ItemsCompletedByDayAndTeam[Day]) 
                    && ItemsCompletedByDayAndTeam[Team] <= MAX(ItemsCompletedByDayAndTeam[Team])
              )
          ))

-- Expression against the ItemsCompletedByDay table
M_CT_2 = CALCULATE
         (
            SUM(ItemsCompletedByDayAndTeam[ItemsCompleted])
            , FILTER
              (
                 ALL(ItemsCompletedByDayAndTeam)
                 , ItemsCompletedByDayAndTeam[Day] <= MAX(ItemsCompletedByDayAndTeam[Day]) 
              )
          )

When they are added to visuals;

Now it shows row values as well as totals accurately.