Table of Contents
Calculating a cumulative sum in Power BI is a process that involves adding up a series of values in a specific order to create a running total. This can be useful for analyzing trends and patterns over time, such as sales data or inventory levels. To calculate a cumulative sum in Power BI, you can use the DAX formula “CUMULATE” which takes the previous values and adds them to the current value. This can be applied to a specific column or measure in your dataset. By using this function, you can easily visualize and analyze cumulative data in your Power BI reports and make informed decisions based on the trends observed.
Calculate a Cumulative Sum in Power BI
You can use the following syntax in DAX to calculate the cumulative sum of values in a particular column in Power BI:
Cumulative Sum = CALCULATE ( SUM ( 'my_data'[Sales] ), ALL ( 'my_data' ), 'my_data'[Date] <= EARLIER ( 'my_data'[Date] ) )
This particular example creates a new column named Cumulative Sum that contains the cumulative sum of the values in the Sales column of the table named my_data.
The following example shows how to use this syntax in practice.
Example: How to Calculate a Cumulative Sum in Power BI
Suppose we have the following table in Power BI named my_data that contains information about sales made on various dates by some company:
Suppose we would like to add a new column that contains the cumulative sum of the values in the Sales column.
To do so, click the Table tools tab along the top ribbon, then click the New column icon:
Then type the following formula into the formula bar:
Cumulative Sum = CALCULATE ( SUM ( 'my_data'[Sales] ), ALL ( 'my_data' ), 'my_data'[Date] <= EARLIER ( 'my_data'[Date] ) )
This will create a new column named Cumulative Sum that contains the cumulative sum of the values in the Sales column:
For example, we can see:
- Cumulative sum of sales for first row: 6
- Cumulative sum of sales for second row: 6 + 9 = 15
- Cumulative sum of sales for third row: 6 + 9 + 13 = 28
- Cumulative sum of sales for fourth row: 6 + 9 + 13 + 12 = 40
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: