Unanswered

Sum column with Filter & group by?

(0) Share

Report

Posted on by bwebmasta

Hi all, I have been going at this for a while. I have a list with the data shown in a gallery:

Name, Award Amount, Month, and Date Awarded. I was able to create a radio control where when the Month is selected, the filter will show only items for that month. Works great, but has a delegation to pop up. The issue is, that a person can be awarded more than once in a month, and I want to show the total awarded in the Awarded column instead of displaying multiples. How would I update the filter to show this? I'm stuck on using Groupby with Sum.

Filter used on gallery : Filter(Awards,Text(Created,"[$-en-US]mmmm") = Radio1.Selected.Value)

In the screenshot, the gallery displayed with radio control:

The second question is, how can I avoid the delegation warnings showing on my total awarded, and total people awarded?

Filter used to show total awarded for the month on the right: Sum(Filter(Awards,Text(Created,"[$-en-US]mmmm") = Radio1.Selected.Value), 'Amount Awarded')

Filter used to display total awarded people:

CountRows(Filter(Awards,Text(Created,"[$-en-US]mmmm") = Radio1.Selected.Value))

Any help would be greatly appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report
@bwebmasta - see below:

Filter( DropColumns( AddColumns( AddColumns( GroupBy( Awards, Name, GroupedItems ), 'Total Awarded', Sum( GroupedItems, 'Award Amount' ) ), 'Date Awarded', First(GroupedItems).'Date Awarded', 'Date Awarded Month', Text( First(GroupedItems).'Date Awarded', "mmmm" ) ), GroupedItems ), 'Date Awarded Month' = Radio1.Selected.Value )

Please note despite the lack of a warning message, "shaping" or "transform" functions like AddColumns and GroupBy are not delegable.

Was this reply helpful? Yes No
bwebmasta 64 on at

Like (0)

Report

I see, I would add this to the Items section on the gallery, correct? I will test it out. Question, how would I be able to use my Month selector (radio button) using this?

Thanks Amik!

I suppose I won't have to worry about delegation, as I set the limit to 1000, and usually in a month about 300-500 are the most that have been submitted to the list.

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (0)

Report

@bwebmasta - yes correct to be applied into the Items property of the Gallery. Note that the syntax above accommodates for the recent change to the required syntax for the GroupBy and AddColumns functions:

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-groupby

Was this reply helpful? Yes No