Power Apps Running Totals In A Gallery

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-apps-running-totals-in-a-gallery/. Author: Matthew Devaney.

Power Apps Running Totals In A Gallery Posted by - Matthew Devaney on - September 9, 2020 13 Comments

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. Month QuantitySold 1/1/2020 10 2/1/2020 20 3/1/2020 15 4/1/2020 15 5/1/2020 40 6/1/2020 40 7/1/2020 15 8/1/2020 50 9/1/2020 15 10/1/2020 50 11/1/2020 25 12/1/2020 35

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) Code language: JavaScript ( javascript )

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). Month Product QuantitySold 1/1/2020 Laptop 2 1/1/2020 Mobile Phone 5 1/1/2020 Tablet 3 2/1/2020 Laptop 4 2/1/2020 Mobile Phone 10 2/1/2020 Tablet 6 3/1/2020 Laptop 4 3/1/2020 Mobile Phone 3 3/1/2020 Tablet 8

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 ) Code language: JavaScript ( javascript ) 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)} ); Code language: PHP ( php )

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 Code language: CSS ( css )

The gallery shows the same running total as before but has better performance. Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up 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. FILTER function Gallery Control SUM function Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps