How To Calculate Cumulative Sum In Power Bi

by

In order to calculate cumulative sum in Power BI, you can make use of DAX functions such as SUMX and CALCULATE along with some measures.

  1. Create a measure to calculate the cumulative sum. Let’s call it “Cumulative Total”.
  2. Use the SUMX function to iterate over the rows of the table and calculate the cumulative sum for each row. The syntax of the function is as follows:
	Cumulative Total = 
		SUMX(
			TOPN(
				RANKX(ALL([YourTable]), [YourColumn], , DESC),
				[YourTable],
				[YourColumn],
				ASC
			),
			[YourColumn]
		)

Here, replace [YourTable] with the name of your table and [YourColumn] with the name of the column for which you want to calculate the cumulative sum.

Let’s consider an example to make it clearer. Suppose you have a table named “Sales” with columns “Month” and “Revenue”. You want to calculate the cumulative revenue for each month.

	Cumulative Revenue = 
		SUMX(
			TOPN(
				RANKX(ALL(Sales), Sales[Month], , DESC),
				Sales,
				Sales[Month],
				ASC
			),
			Sales[Revenue]
		)

This measure will calculate the cumulative revenue for each month based on the ranking of the months in descending order.

Read more

Leave a comment