Totaling items in a gallery?

[Cooper01]

I have a gallery formatted as a table which gives a run down of hours worked for different departments in my organization stored in CDS. Right now the table looks something like this:

District	Role	Hours
West	HR	5
West	IT	10
East	HR	14
West	IT	20

The Items property of this gallery is filtering all items from an Employees entity where Hours is greater than 0:

Filter(Employees,HoursWorked > 0)

The district, role, and hours values are all fields in my Employee entity.

As a next step, I'd like to simplify the table so that if the District and Role are the same, the Hours column will show a total for all employees, like this:

District	Role	Hours
West	HR	5
West	IT	30
East	HR	14

I'm not really sure how to approach this. I was trying to iterate through the Employees table and build a collection conditionally checking if the District and Role already exist, but as this is just a subset of the data for all employees this feels inefficient. I also tried group by but as District AND Role are relevant, I'm not quite sure how to do that. Is there any other way to manipulate the Filter to sum the Hours column?

[eka24]

Make use of Groupby functions. Video from Brian Knight
https://youtu.be/q42ibovAqZc

Also:

1. Insert a Gallery1 and set its items Property to: GroupBy(Employees,"District","HourSum")

1. In one of the Textbox inside the Gallery1 Set it Text to: ThisItem.District
2. In one of the Textbox2 inside the Gallery1 Set it Text to: ThisItem.Role
3. In Another Textbox3 INSIDE the Gallery1, put this formula: Sum(HourSum,Hours)

If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

[Cooper01]

Thank you for the response. The total hours is working but I am not able to access ThisItem.Role

[Cooper01 · Accepted solution]

Needed to add the field to the group by. My final Items value ended up as: 

GroupBy(
        Filter(
            Employees,
            HoursWorked > 0
        ),
        "District",
        "Role",
        "VisitSum"
    )