Table of Contents
Power BI is a powerful data analysis tool that allows users to gather insights from their data. One of its useful features is the ability to calculate the cumulative sum by category. This means that users can easily determine the total sum of a specific category over a period of time. This can be achieved by using the “Cumulative Total” function in Power BI, which takes into account the values of the selected category and adds them up in chronological order. This feature is beneficial for businesses and organizations looking to track their progress or monitor trends within different categories. With Power BI, users can efficiently and accurately calculate the cumulative sum by category to gain valuable insights from their data.
Power BI: 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 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: