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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,838)