There is often the need to display running totals in apps, particularly in financial type apps. As an example, here's how to show the running total of costs on a gallery of invoices. Here's a screenshot of our invoices table (called '[dbo].[Invoices]').

To display a running total add a gallery control and add labels to display the price and running total. Set the properties of these controls to the following:

BrowseGallery1.Items = SortByColumns(Invoices,"InvoiceID")
lblPrice.Text = Text(ThisItem.Price, "0.00")
lblRunningTotal.Text = Sum(Filter('[dbo].[Invoices]',InvoiceID<=ThisItem.InvoiceID),Price)

Our gallery control displays Invoices in ascending InvoiceID order. Note that for this technique to work, it's necessary to sort the gallery data by a unique numerical field.

Any row in our gallery will display a record that with a given InvoiceID number. To obtain a running total, the formula in our lblRunning label calls the Filter function to return all records with an InvoiceID number less than or equal to the InvoiceID of the current row. The formula then calls the Sum function on this result set to calculate the sum of the price field, which equates to the running total of the price field in the gallery.

Here's a screenshot of the result.

As you'll notice, the unfortunate thing about this technique is that it's not delegable, and the results will be incorrect if the source data exceeds the maximum 2000 record limit.

The workaround for this is to collect the records that you want to display into a local collection, and to set the items property of the gallery control to the local collection. As a side note, it's not possible to fix the delegation issue by calling Sum/Filter on BrowseGallery1.AllItems instead of '[dbo].[Invoices]'. Because we call this from within the gallery template, this would result in a circular reference error.