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!

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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