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 Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,869)