How to Calculate Running Total in Power BI?

This Power BI tutorial helps you to understand how to calculate the running total in Power BI.

Recently, I worked on a Power BI Report where I needed to find the total of a series of values over time.

In this tutorial, I will explain what a Power BI running total measure is and how to calculate running total in Power BI. Also, we will see how to create a running total using quick-measure suggestions in Power BI.

What is the Running Total in Power BI

In Power BI, a running total is similar to keeping track of items and adding them up as you go. This computation displays the total values for a certain period.

Suppose you have sales data for each month and year. A running total would continuously add up the sales from the beginning of the year to the current year, giving you a running sum. Check the screenshot below.

What is Running Total Measure in Power BI

How to Calculate Running Total in Power BI

Now, I will tell you how to calculate the running total of a measure in Power BI.

Here, I have a SharePoint list (Orders) that contains below columns with various data types:

ColumnsData Types
Order DateDate and time
Profuct NameSingle line of text
CategorySingle line of text
Sub-CategorySingle line of text
SalesCurrency
ProfitCurrency
running total measure power bi

Follow the below steps to create a running total measure in Power BI:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

running total of a measure in power bi

2. Under the Home tab, click the New measure.

power bi running total measure

3. In the formula bar, put the below expression.

Monthly Running Total = 
CALCULATE(
		SUM(Orders[Sales]),
		FILTER(
			ALL('Orders'),
			'Orders'[Order Date] <= MAX('Orders'[Order Date])
		)
	)

Find the last day of the month, MAX (Orders'[Order Date]). Then, see how many dates in the entire Orders (date column) are less than equal to the last day of the month. Then, calculate the Sales for all Dates that match the above criteria.

Where:

  • Monthly Running Total = Name of the Measure
  • CALCULATE = DAX Function
  • SUM = DAX Function
  • Orders = Name of the Table
  • Sales = Name of the Column
  • FILTER = DAX Function
  • ALL = DAX Function
  • Order Date = Name of the Column
running total in power bi

4. Click the Home tab -> Expand Visual gallery(black box) -> Click the Matrix visual.

Create Running Total On A Calculated Measure in Power BI

5. Then, using the +Add data option, add the Order Date into the Rows field and Sales and Monthly Running Total into the Values field.

running total in power bi measure

This way, you can calculate the running total of a measure in Power BI.

Power BI Running Total Quick Measure

Now, I will tell you how to create a running total using quick measure in Power BI.

1. Under the Home tab, click the Quick measure.

running total quick measure power bi

2. Then a Quick measure pane opens, expand the Select a calculation. Select the Running total.

power bi quick measure running total

3. Using +Add data, add Base value as Sales, Field as Order Date – Month, and Direction as Ascending. Then click Add.

quick measure running total power bi

4. Then you see the Sales running total in Month (Quick measure) added in the Data pane. When selected (Sales running total in Month), you can observe the DAX expression in the formula bar.

Sales running total in Month = 
CALCULATE(
	SUM('Orders'[Sales]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'Orders',
				'Orders'[Order Date].[MonthNo],
				'Orders'[Order Date].[Month]
			),
			ALLSELECTED('Orders')
		),
		ISONORAFTER(
			'Orders'[Order Date].[MonthNo], MAX('Orders'[Order Date].[MonthNo]), DESC,
			'Orders'[Order Date].[Month], MAX('Orders'[Order Date].[Month]), DESC
		)
	)
)
power bi running total quick measure

5. Click the Home tab -> Expand Visual gallery(black box) -> Click the Matrix visual.

Create Running Total On A Calculated Measure in Power BI

6. Then, using the +Add data option, add the Order Date into the Rows field and the Sales and Sales running total in Month into the Values field.

power bi cumulative sum quick measure

7. After that, you can see the running total in the table visual.

power bi running total quick measure suggestions

This way, you can create a Power BI running total in quick measure.

Conclusion

I hope this tutorial helps you to understand the Power BI running total.

In this tutorial, we learned what a Power BI running total measure is and how to calculate the running total of a measure in Power BI. Additionally, we will see how to create a running total using quick-measure suggestions in Power BI.

Also, you may like some more Power BI articles: