How to do group by and calculate sum in Power Apps?

This Power Apps tutorial will go over how to group data and calculate the sum of those grouped items within Power Apps. We’ll use the PowerApps Groupby() and Sum() functions to group the data and determine the sum of the grouped items.

I was recently working in a canvas app in Power Apps, where we displayed items from a collection using a gallery control. Here we have the requirement to group the items and calculate their sum.

Like, we have an hour column, and we wanted to display the total of that hour column based on the users.

To meet this requirement, the following steps are:

Group by and calculate the sum

Let us understand, how to calculate the sum of the grouped gallery items within the Power Apps. That is, we will use Power Apps to group the items and calculate the sum based on the employee names and hours.

Requirement:

The images below show the various types of companies that have repetitive employees working different hours.

When the user clicks on the button, the collected data is grouped by the employees’ names, and the sum is calculated based on the respective employees’ working hours.

• Here, we have created a collection in the Power Apps screen’s OnVisible property. To create a collection in Power Apps, insert the following expression on the screen’s OnVisible property.

OnVisible = ClearCollect(ColCompany,{Company:"Microsoft",Employee:"Albert",Hours:5},
{Company:"Google",Employee:"John",Hours:8},
{Company:"Amazon",Employee:"Robert",Hours:7},
{Company:"Amazon",Employee:"Robert",Hours:4},
{Company:"Google",Employee:"David",Hours:5},
{Company:"Microsoft",Employee:"Sonam",Hours:6},
{Company:"Microsoft",Employee:"Sonam",Hours:3},
{Company:"Google",Employee:"David",Hours:8},
{Company:"Microsoft",Employee:"Albert",Hours:7},
{Company:"Microsoft",Employee:"Albert",Hours:9})

Where,

1. ColCompany is the name of the Power Apps collection.
2. Company, Employee, and Hours are the names of the collection’s headers.

• Add a vertical gallery to the Power Apps screen, to display the collection i.e., ColCompany. Go to Inserts > Vertical gallery > connect it with the collection i.e., ColCompany.

• Next, add a button control to the screen and insert the following expression on the button’s OnSelect property to group the collected data by employee names and calculate the sum of each employee’s working hours.
• Also, give a proper name to the button control. (Ex: Click here to group & calculate sum)

OnSelect = ClearCollect(
    ColGroupedData,
    DropColumns(
        AddColumns(
            GroupBy(ColCompany,"Employee","GroupedEmployees"),
        "Sum of Value",Sum(GroupedEmployees,Hours)
        ),
    "GroupedEmployees"
    )
);

Where,

1. ColGroupedData is the name of the newly created collection to store the grouped data.
2. ColCompany is the name of the existing collection.
3. Employee is the name of the existing collection column that we want to group by.
4. GroupedEmployees is the name of the newly created column name to store the grouped data.

• To display the collected data, add another gallery to the Power Apps screen and connect that gallery with the newly created collection (i.e., ColGroupedData).
• The collection may not be visible on the screen at first. Make sure to click the button once the collection (i.e., ColCompany) has been created. As a result, the grouped data is created and displayed in the gallery.

This is how to use group the data and calculate their sum on the Power Apps gallery.

Conclusion

From this Power Apps tutorial, we have learned how to calculate the sum of the grouped data within Power Apps.

You may like the following Power Apps tutorials:

• Power Apps Timer Control Examples
• How to calculate sum of gallery items in Power Apps?
• Power Apps Notify() function
• Power Apps Filter With Date Picker
• How to get current date in Power Apps?
• Power Apps Get Value Of Choice Field Dataverse