Power Apps Running Totals In A Gallery

A running total shows the sum of all previous values in a sequence. One common example where running totals in are useful in Power Apps is when you want to display monthly sales data along with a year-to-date sum. There is not any built-in function for running totals so I will show you how to make them yourself.

Running Totals On A Single Column

The ‘Sales By Month’ SharePoint list has two columns: Month (Date type) and QuantitySold (Number type). We want to add a running total to track the year-to-date QuantitySold.

Create a gallery in PowerApps using ‘Sales By Month’ as the datasource. Include labels showing the Month and Quantity Sold.

Insert another label to show the running total with this code in the Text property. You will receive a delegation warning but this is expected. The running totals will calculate properly assuming the data returned by the FILTER function is 2,000 rows or less.

Sum(Filter('Sales By Month', Month<=ThisItem.Month), QuantitySold)

The gallery now shows the running total.

Running Totals On Multiple Columns

A running total can also be performed on multiple columns. Our next goal will be to create a running total for each Product sold by Month. The ‘Sales By Product’ SharePoint list shown below includes the new column Product (Text type).

Make a new gallery in PowerApps using ‘Sales By Product’ as the datasource. Place labels inside the gallery to show Month, Product and Quantity Sold.

Create another label to show the running total by product and use this code in the Text property.

Sum(
    Filter('Sales By Product',
        Product=ThisItem.Product,
        Month<=ThisItem.Month
    ), QuantitySold
)

The final result looks like this.

Improving Performance

Calculating the running total can appear slow because Power Apps is making a separate call to the datasource for each row in gallery. The performance monitor in Power Apps Studio shows 12 responses for our 1st example: Running Totals On A Single Column.

If the datasource ‘Sales By Month’ has 2,000 rows or less we can improve performance by downloading the datasource into a collection then calculating the running total on it.

// download the datasource and add a running total column
ClearCollect(colRunningTotal, AddColumns('Sales By Month',"RunningTotal",0));

// calculate the running total for each row
UpdateIf(
    colRunningTotal As Table1,
    true,
    {RunningTotal: Sum(Filter(colRunningTotal, Month<=Table1.Month), QuantitySold)}
);

By checking the performance monitor we can see only 1 call was made, with a shorter duration and smaller response size.

To show the running total in the gallery we insert a new label and use this code in the Text property instead.

ThisItem.RunningTotal

The gallery shows the same running total as before but has better performance.

Questions?

If you have any questions or feedback about Power Apps Running Totals In A Gallery please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.