cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver IV
Resolver IV

Totaling items in a gallery?

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:

DistrictRoleHours
WestHR5
WestIT10
EastHR14
WestIT20

 

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:

DistrictRoleHours
WestHR5
WestIT30
EastHR14

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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

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

View solution in original post

3 REPLIES 3
Super User III
Super User III

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.

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

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

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

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (49,451)