cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iSpeak
Frequent Visitor

GroupBy and multiple Sum columns

I have a SP list "classes" with columns "subject_id", "academic_hrs" and "actual_hrs" similar to the following...

 

 

ID     subject_id    academic_hrs   actual_hrs
1       subject_1         4             3
2       subject_2         4             3
3       subject_2         4             3
4       subject_1         4             3
5       subject_2         4             3
6       subject_1         4             3
7       subject_2         4             3

 

 

I need a table that aggregates all the classes to see something like the following...

 

 

subject_id     row_count      sum_academic_hrs     sum_actual_hrs
subject_1      3                    12             9
subject_2      4                    16             12

 

 

I've been trying to do this with the GroupBy function, but can't figure out how to get both the row_count AND the summed columns. Please show me how this can be accomplished.

 

My attempt...

 

AddColumns(
    GroupBy(
        classes,
        "subject_id",
        "all_subjects"
    ),
    "row_count",
    CountRows(all_subjects)
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

You can add multiple columns at once in a single AddColumns call. In your case, you can have an expression like the one below:

AddColumns(
    GroupBy(
        classes,
        "subject_id",
        "all_subjects"
    ),
    "row_count",
    CountRows(all_subjects),
    "sum_academic_hrs",
    Sum(all_subjects, academic_hrs),
    "sum_actual_hrs",
    Sum(all_subjects, actual_hrs)
)

Hope this helps!

View solution in original post

4 REPLIES 4
CarlosFigueira
Power Apps
Power Apps

You can add multiple columns at once in a single AddColumns call. In your case, you can have an expression like the one below:

AddColumns(
    GroupBy(
        classes,
        "subject_id",
        "all_subjects"
    ),
    "row_count",
    CountRows(all_subjects),
    "sum_academic_hrs",
    Sum(all_subjects, academic_hrs),
    "sum_actual_hrs",
    Sum(all_subjects, actual_hrs)
)

Hope this helps!

View solution in original post

That worked perfectly. The part I was missing was adding "all_subjects" to the function...

Sum(all_subjects, academic_hrs)

Thanks for the help!

Hi all!

 

I've tried what you suggest but althought there is no error in the formula the output for the sum is empty. 

I need a table grouped by employee ID with the sum of the duration of their contracts. 

 

My data source is a SP list where duration is a calculated column. 

 

The formula I have used in the table is:

 

AddColumns(
GroupBy(
'Contracts';
"Employee_ID";
"Employee"
);
"TotalDuration";
Sum(
Employee;
'Duration'
)

 

Hope any of you could help me! I would really appreciate it. 

 

Thanks!

@CarlosFigueira 

 

Thanks for your suggestion, but can we do this in Model-Driven App.

 

Capture.PNG

 

I have 1000+ records for single resource and want to SUM of their hours.

 

Any kind of help will appreciate.

 

Regards, 

Mayank

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,829)