Latest Enterprise DNA Initiatives


Cumulative Total (Group-based)

Cumulative totals are most often presented over a date-basis, as is the case with the existing DAX pattern for cumulative totals.

There are situations, however, where it may be of interest to present a cumulative total not over a date-base but over a group-base. This can be achieved easily in DAX though the use of a modified version of the dynamic grouping pattern.

Here, a supporting table can be created with the groups of interest, and then the fact table rows can be accumulated within each group. For example, if one wanted to group sales into revenue groups, one could create a Revenue Groups supporting table as:


The number of sales could then be accumulated into these groups using the dynamic grouping DAX pattern as follows:

# of Sales in Revenue Group = 
CALCULATE(
    COUNTROWS( Sales ),
    FILTER(
        VALUES( Sales[Revenue] ),
        COUNTROWS(
            FILTER(
              'Revenue Groups',
                AND(
                    Sales[Revenue] >= 'Revenue Groups'[Min],
                    Sales[Revenue] <= 'Revenue Groups'[Max]
                )
            )
        ) > 0
    )
)

Which could also be expressed as a percentage of sales:

% of # of Sales in Revenue Group = 
VAR _TotalCount = COUNTROWS( ALL( Sales ) ) 
VAR _CurrentCount = [# of Sales in Revenue Group] 

RETURN
DIVIDE( _CurrentCount, _TotalCount, 0 )

And, finally, a cumulative percent could be expressed for the groups:

Cumulative % of # of Sales in Revenue Group = 
VAR _TotalCount = COUNTROWS( ALL( Sales ) ) 
VAR _CurrentCount = 
CALCULATE(
    [# of Sales in Revenue Group],
    FILTER(
        ALLSELECTED( 'Revenue Groups' ),
      'Revenue Groups'[Max] <= MAX( 'Revenue Groups'[Max] )
    )
) 

RETURN
IF(
    ISBLANK( [# of Sales in Revenue Group] ),
    BLANK(),
    DIVIDE( _CurrentCount, _TotalCount, 0 )
)


These values can also be presented for the number of customers who made the purchases for those sales.

Another example is included in the sample PBIX file for accumulating sales into Quantity Groups.
DAX Patterns - Cumulative Total.pbix (604.8 KB)

2 Likes

Just adding some keywords to make this easier to locate via forum search:
DAX pattern, aggregate, cumulative, total, group, group-based