Power BI: How to Calculate Cumulative Sum by Category


You can use the following syntax in DAX to calculate the cumulative sum of values by category in Power BI:

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

This particular formula creates a new column named Cumulative Sum that contains the cumulative sum of values in the Points column, grouped by the values in the Team column.

This formula assumes that you have created an Index column that ranges from 1 to N.

The following example shows how to use this formula in practice.

Example: How to Calculate Cumulative Sum by Category in Power BI

Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:

Suppose we would like to create a new column that shows the cumulative points scored by players on each team.

Before we do so, we need to first add an index column to the table.

To do so, click the Home tab along the top ribbon, then click the Transform data icon:

This will bring up the Power Query Editor.

Next, click the Add Column tab, then click the dropdown arrow next to Index Column and choose whether or not you’d like the index values to start at 0 or 1:

We’ll click From 1 so that the following index column is added:

Once you exit out of the Power Query Editor, a message box will appear that asks if you’d like to apply your changes.

Once you click Yes, the index column will be added to the table.

Lastly, to add a new column that shows the cumulative points by team, click the Table tools tab, then click the New column icon:

Then type the following formula into the formula bar:

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

A new column named Cumulative Sum will be created that shows the cumulative sum of values in the Points column for each team:

Power BI cumulative sum by category

The new Cumulative Sum column shows the cumulative sum of values in the Points column for each team.

Additional Resources

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

How to Write a Case Statement in Power BI
How to Calculate Percent of Total in Power BI
How to Calculate Percent of Total by Category in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *