Table of Contents
To calculate the cumulative sum by category in Power BI, you can use the DAX function “CALCULATE” in combination with the “FILTER” function. First, you need to create a measure that calculates the sum of the values for each category. Then, you can use the “CALCULATE” function to add the current row’s value to the previous row’s cumulative sum. The “FILTER” function is used to filter the results by category. This will create a running total for each category, allowing you to see the cumulative sum over time. You can then visualize this data in a chart or table to analyze the trends and patterns within each 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 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:
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: