How to Calculate Cumulative Sum by Category in Power BI

The following standard syntax in DAX is used to calculate the cumulative sum of values, grouped by a specific categorical field. Note that this approach requires a unique index column for accurate ordering within the calculation context:

Cumulative Sum = 
CALCULATE (
    SUM ( 'my_data'[Points] ),
    ALLEXCEPT ( 'my_data', 'my_data'[Team] ),
    'my_data'[Index] <= EARLIER ( 'my_data'[Index] )
)

This specific formula defines a calculated column named Cumulative Sum. It instructs CALCULATE to sum the values in the [Points] column. The key to the cumulative calculation lies in the filter arguments:

• ALLEXCEPT ( ‘my_data’, ‘my_data'[Team] ): This filter removes all existing filters from the table my_data, except for any filters applied to the [Team] column. This is what preserves the category context, ensuring the running total resets for each new team.

• ‘my_data'[Index] <= EARLIER ( ‘my_data'[Index] ): This crucial filter iterates through the table. For the current row being evaluated, it ensures that only rows where the index is less than or equal to the index of the current row are included in the sum. This creates the running total effect.

It is essential to understand that this formula relies entirely on the existence and proper ranging of the Index column, which typically ranges sequentially from 1 to N across your data rows.

Prerequisite: Creating the Sequential Index Column

Before implementing the DAX cumulative sum formula, a necessary structural element must be added to your data model: a sequential index column. This column provides the numerical ordering required by the EARLIER function to accurately determine which rows precede the current row during the calculation of the running total. Without this numerical index, the comparison logic (less than or equal to) within the filter context cannot function correctly.

While it might seem simple, the index column is the backbone of time-insensitive running totals in Power BI. It must be created and managed within the data transformation phase, ideally using the Power Query Editor, to ensure it is robust and stable before the DAX engine processes it. Creating the index in Power Query ensures that the values are fixed during data loading, preventing unexpected calculation behavior later on.

In practical terms, the Index column serves as a substitute for a naturally occurring chronological field when the requirement is simply to track accumulation based on the physical order of rows within a system or report. For category-based calculations, it is paramount that this index is unique across all rows, facilitating the precise row-by-row aggregation needed for the running total.

Example Scenario: Calculating Team Points

To illustrate this process clearly, consider a practical scenario involving sports data. Suppose we are analyzing a table in Power BI, named my_data, which records the points scored by various basketball players across different teams. We aim to determine the total points accumulated by each team up to a specific row entry.

The initial structure of the data table my_data contains key fields such as Player, Team (our category), and Points (the value to be summed). Visualizing this initial state helps set the context for the transformation process:

Our objective is to augment this table by creating a new column that meticulously tracks the cumulative sum of the points scored. This calculation must intelligently reset itself whenever the team category changes, providing a running total specific to each team rather than a grand total across all teams.

Step-by-Step Guide: Adding the Index Column via Power Query

As established, the first step is adding the sequential index column. This must be handled in the data transformation phase.

Accessing the Power Query Editor

To begin, navigate to the main Power BI Desktop ribbon. Click the Home tab, and then locate and click the Transform data icon. This action launches the separate window environment known as the Power Query Editor, which is where data shaping and preparation occurs.

Inserting the Index Column

Once inside the Power Query Editor, select the Add Column tab from the top menu ribbon. Within this section, find the Index Column dropdown. You will be prompted to choose the starting point for the index values. Although starting from 0 is common in programming, starting From 1 often provides a clearer, more readable sequence for basic analytical tasks.

We select From 1 to create a numerical column ranging from 1 up to the total number of records. After applying this step, the Index column is appended to the data table, ensuring every row has a unique identifier for ordering:

Applying Changes and Returning to Power BI Desktop

After successfully adding the index column, you must apply the changes and load the transformed data back into the Power BI Data Model. In the Power Query Editor, navigate back to the Home tab and click Close & Apply. This finalizes the data preparation, making the new Index column available for use in subsequent DAX calculations.

Implementing the Categorized Cumulative Sum in DAX

With the Index column now available in the data model, we can proceed to define the actual categorized running total calculation using DAX. Since a running total is a row-by-row calculation that depends on the context of other rows, it is best implemented as a Calculated Column.

Creating the New Calculated Column

In the Power BI Desktop interface, ensure you are in the Data view or Model view. Select the table (my_data) to which you want to add the column. Then, find the Table tools tab in the ribbon (it may appear after selecting the table) and click the New column icon.

Defining the DAX Logic

The formula bar will appear, allowing you to input the DAX expression. Carefully input the cumulative sum formula designed to group by the [Team] category:

Cumulative Sum = 
CALCULATE (
    SUM ( 'my_data'[Points] ),
    ALLEXCEPT ( 'my_data', 'my_data'[Team] ),
    'my_data'[Index] <= EARLIER ( 'my_data'[Index] )
)

Upon pressing Enter, Power BI executes this expression for every row in the my_data table. For each row, the cumulative sum is calculated by summing all preceding and current points, strictly filtered by the current team.

Analyzing and Validating the Results

The result of executing the CALCULATE expression is a new column named Cumulative Sum. This column demonstrates the desired categorized running total functionality.

Observe how the Cumulative Sum progresses linearly for Team A (10, 25, 30). Crucially, when the table transitions to Team B, the cumulative sum resets (starts at 5). This confirms that the ALLEXCEPT ( 'my_data', 'my_data'[Team] ) argument successfully preserved the category filter while allowing the row context to iterate correctly based on the Index.

This resulting column is now ready for visualization. You can plot this new column against the index or another sequential field in a line chart to visualize the growth rate or trend of points scored for each team, offering far greater analytical depth than simple aggregated measures.

Further Applications and Related DAX Tasks

While this tutorial focused on calculating the cumulative sum by category using an index, the underlying principles of context transition and filter modification established by the CALCULATE function are applicable to numerous advanced DAX tasks. Understanding how ALLEXCEPT and EARLIER interact is key to solving complex filtering problems where you need to reference a previous state or a specific context.

Common related tasks that use similar DAX logic include calculating year-over-year growth rates, performing moving averages, or creating rank columns that dynamically adjust based on user-selected filters. Mastering the indexed cumulative sum provides a strong foundation for tackling these more complex modeling challenges.

The following tutorials explain how to perform other common tasks in Power BI: