Group by gallery doesn't return column to sum up

(0) Share

Report

Posted on by dasiekr

Hello,

What I have is a galleryGrid gallery which is populated with the data from colGridData.

This collection consists of multiple columns but the most important is Title, and field_3 (which in my sharepoint list is called Circle).

Below is a sample based on these two columns.

Title	Circle (field_3)
1H2021	10
1H2021	16
2H2021	32

I need to perform following operations on these data:

1) Firstly, I group by this collection so after this step I get:

Title	Circle (field_3)
1H2021	26
2H2021	32

2) Secondly, I need to take the average of this two rows so my final result will be 29.

I thought that I found what I was looking for and I've tried to use this query in different forms but each time I'm not able to receive field_3 column from Data to sum it up and then proceed with these values.

AddColumns(
GroupBy(
galleryGrid.AllItems,
"Title", "Data"),
"Total Circle",
Sum(Data.field_3)
)

It seems like a problem is I can not access anything from "data" group.

I started using PowerApps a week ago so I'm not familar with some of concepts.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

WiZey 3,023 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hello @dasiekr ,

You don't need to add "Sum()" here. "GroupBy()" will create a sub-table inside you collection under the "data" column you specified, and this sub-table will contain every rows under the same "Title".

ClearCollect( cltNewCollection, GroupBy( cltOldCollection, "Title", "Data" ) )

Then only after can you use an algebra to compute the "Sum()" and the "Average()".

Average( ForAll( cltNewCollection, Sum( ThisRecord.Data, Circle ) ), Value )

Hope this was helpful to you.

Was this reply helpful? Yes No
dasiekr 24 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WiZey ,
This seems like something that will do the trick but is there a way to avoid creating another collection?
Just one formula that will return that value?
I would rather not make my app more complex.

Was this reply helpful? Yes No
Verified answer

WiZey 3,023 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Sure, you just have to provide the "GroupBy()" to the "ForAll()" instead of using a new collection.

Average( ForAll( GroupBy( collection, "Title", "Data" ), Sum( ThisRecord.Data, Circle ) ), Value )

Was this reply helpful? Yes No
dasiekr 24 on at

Like
a
(0)

Report
Copy link

Link copied!

@WiZey I'm not sure how I check it for the 1st time but now I have my former issue.
In you query when I refer to ThisRecord.Data there is no Circle column available - only Title and Data.
Could you help me with this?

Was this reply helpful? Yes No
WiZey 3,023 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Sure, could you show me the code you wrote please? Maybe I missed something on my side.

In the "Sum()" function, you can't use "ThisRecord.Data"? What if you use "Data" instead of "Circle" then? Does "Data" have any "Circle" property then?

Was this reply helpful? Yes No