Filter and Count Rows in Nested Gallery

[Annasuela_]

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:

Nested Gallery:

Code:

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

Image:

Items property of the nested Gallery: 

Thank you in advance. 

Anna 

[rubin_boer · Accepted solution]

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

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

 

Both methods

 

[Annasuela_]

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