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.
