cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Annasuela_
Helper III
Helper III

Filter and Count Rows in Nested Gallery

Hi Team - I have created a nested gallery where I've grouped two sections by "Tech" and "Prod." and provided the total headcount. However, the headcount is also filtered in another column by the following categories: Open, Filled, Vacant, Eliminated.

 

How do I filter the total rows of each category (Open, Filled, Vacant, Eliminated) in a nested gallery that is grouped by two selections? Please see below for additional information. 

 

First Gallery:

Code: 

AddColumns(
GroupBy(
TechProdHCTracker,
"field_4",
"DataLead"
),
"GroupTotal",
CountRows(DataLead)
)

Image:

Annasuela__3-1663181746815.png

 

 

Nested Gallery:

Code:

AddColumns(
GroupBy(
TechProdHCTracker,
"field_4",
"field_6",
"DataLead"
),
"GroupTotal",
CountRows(DataLead)
)

Annasuela__4-1663181768610.png

 

Image:

 

 

Items property of the nested Gallery: 

Annasuela__5-1663181790254.png

 

Thank you in advance. 

 

Anna 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

hi @Annasuela_ 

 

there are a few ways to go about this and I will share 2.

 

the data source is colTech which is {group, category}

Method 1

Group the data as follows:

GroupBy(
    AddColumns(
        GroupBy(colTech, "group", "category", "Unit"), "Group Total", CountRows(Unit))
        , "group", 
        "Group Total"
    )

 

Add a gallery with its Items = the above code

add labels for the totals

lblGroup = Sum(ThisItem.'Group Total', 'Group Total')

lblOpen = Sum(Filter(ThisItem.'Group Total', category = "Open"), 'Group Total') 

lblFilled = Sum(Filter(ThisItem.'Group Total', category = "Filled"), 'Group Total') 

lblVacant = Sum(Filter(ThisItem.'Group Total', category = "Vacant"), 'Group Total') 

lblElim = Sum(Filter(ThisItem.'Group Total', category = "Eliminated"), 'Group Total') 

 

Add labels for the headers as well (yip here you have many headers :))

Result

rubin_boer_0-1663438274209.png

 

Method 2

AddColumns(
    GroupBy(colTech, "group", "Unit"), 
    "Group Total", 
    CountRows(Unit)
    )

 

Add a gallery with its Items = the above code

add labels for the totals

lblHeader = ThisItem.group

lblTotal = ThisItem.'Group Total'

 

Nest a gallery

Set its Items = AddColumns(GroupBy(ThisItem.Unit,"category","categories"),"Category Total", CountRows(categories))

add 2 labels

lblSubHeader = ThisItem.category

lblSubTotal = ThisItem.'Category Total'

Result

Screenshot 2022-09-17 201759.png

Both methods

Screenshot 2022-09-17 201959.png

 

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

View solution in original post

2 REPLIES 2
rubin_boer
Super User
Super User

hi @Annasuela_ 

 

there are a few ways to go about this and I will share 2.

 

the data source is colTech which is {group, category}

Method 1

Group the data as follows:

GroupBy(
    AddColumns(
        GroupBy(colTech, "group", "category", "Unit"), "Group Total", CountRows(Unit))
        , "group", 
        "Group Total"
    )

 

Add a gallery with its Items = the above code

add labels for the totals

lblGroup = Sum(ThisItem.'Group Total', 'Group Total')

lblOpen = Sum(Filter(ThisItem.'Group Total', category = "Open"), 'Group Total') 

lblFilled = Sum(Filter(ThisItem.'Group Total', category = "Filled"), 'Group Total') 

lblVacant = Sum(Filter(ThisItem.'Group Total', category = "Vacant"), 'Group Total') 

lblElim = Sum(Filter(ThisItem.'Group Total', category = "Eliminated"), 'Group Total') 

 

Add labels for the headers as well (yip here you have many headers :))

Result

rubin_boer_0-1663438274209.png

 

Method 2

AddColumns(
    GroupBy(colTech, "group", "Unit"), 
    "Group Total", 
    CountRows(Unit)
    )

 

Add a gallery with its Items = the above code

add labels for the totals

lblHeader = ThisItem.group

lblTotal = ThisItem.'Group Total'

 

Nest a gallery

Set its Items = AddColumns(GroupBy(ThisItem.Unit,"category","categories"),"Category Total", CountRows(categories))

add 2 labels

lblSubHeader = ThisItem.category

lblSubTotal = ThisItem.'Category Total'

Result

Screenshot 2022-09-17 201759.png

Both methods

Screenshot 2022-09-17 201959.png

 

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
Annasuela_
Helper III
Helper III

Hi @rubin_boer  - Thank you so very much. This was very helpful and it worked! 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,727)