Data - How to group data in a gallery and calculate sums
When displaying records in a gallery, it can be very helpful to show the sums of values. This post provides a walk-through on how to carry out this task.
Example Data Structure
Showing parent records in a gallery
To begin, we'll add a gallery control to display the customer details. To do this, we add a gallery control and set the Items property to the customer table, as shown in the screenshot below.
To display the related order records for each customer, we add a nested gallery inside the customer gallery. In this example, the name of the gallery is galOrders.
To do this, we set the Items property of the gallery to the following formula. This filters the gallery to return Orders where the CustomerID of the Customer record matches the CustomerID in the Orders table.
How to show a subtotal of gallery values
To display the sum of the order prices grouped by customer, we can add a label to the parent galCustomers gallery. We can then use the following syntax to calculate the sum.
Sum(galOrders.AllItems, Price)With this formula, we call the Sum function against the datasource galOrders.AllItems. For each row in galCustomers, galOrders.AllItems returns the related order records since galOrders is filtered by CustomerID.
- Formula - How to add a button that converts degrees Centigrade to Fahrenheit and vice versa
- Formula - How to convert a single delimited string to rows and columns
- Formula - How to calculate compound interest
- Utilities - The best way to peform OCR on images of Power Apps Formulas
- Example - How to use a drop down control to convert currencies
- Formula - How to parse JSON in Power Apps- 4 examples
- Data - How to get a row by ordinal number
- Formula - What to do when the If statement doesn't work?
- Formula - Boolean And / Or operators - What is the order of precedence?
- Controls - How to set the data source of a Combo Box to a comma separated string
- Numbers - 10 examples of how to round numbers
- Formula - Difference between round, square, and curly brackets
- Top 3 highlights of upcoming enhancements to the Power Apps language (Power FX)
- Email - Sending email attachments with the Office 365 Outlook connector
- Formula - What to try when numbers don't format correctly
- Controls - How to convert HTML to Text
- Formulas - how to return all days between two dates
- Formula - How to create comma separated (CSV) list of items
- Formula - How to use the IF and Switch functions - 3 common examples
- Location - Finding the closest location and and sorting records by distance, based on the current location of the user
- Formulas - How to cope with weekends and public holidays in date calculations