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

Creating a new summarized collection based on a query

Hello, all.

 

I'm pulling data from my SQL Server in this format:

OriginalSales.PNG

 

I'd like to create another Collection grouped by DEPARTMENT and would summarize the Sales, like the image below:

 

Sales Sumarized.PNG

 

Does anyone may help me?

 

thanks,

Paulo.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power Apps
Power Apps

Re: Creating a new summarized collection based on a query

You can use the GroupBy function to do that - there are some examples in the documentation page that show exactly how to do that. In your case, you're probably looking at something like the expression below:

AddColumns(
    GroupBy('[dbo].[YourTableName]', "Department", "ByDepartment"),
    "Sales",
    Sum(ByDepartment, Sales))

View solution in original post

4 REPLIES 4
Highlighted
Power Apps
Power Apps

Re: Creating a new summarized collection based on a query

You can use the GroupBy function to do that - there are some examples in the documentation page that show exactly how to do that. In your case, you're probably looking at something like the expression below:

AddColumns(
    GroupBy('[dbo].[YourTableName]', "Department", "ByDepartment"),
    "Sales",
    Sum(ByDepartment, Sales))

View solution in original post

Highlighted
Super User
Super User

Re: Creating a new summarized collection based on a query

Hi !

You can use a combncombin of GroupBy(), AddColumns() and DropColumns() functions :

 

ClearCollect(
SalesByDepartment,

GroupBy( OriginalCollection, "Department", "MonthSales" ) ) ClearCollect( DepartmentSalesSum, AddColumns( SalesByDepartment "Sum of departments sales", Sum(MonthSales, Sales ) ) ) ClearCollect( DepartmentSalesSumOnly, DropColumns( DepartmentSalesSum, "MonthSales" ) )

 First, Group your initial table By department.

Second, Add a Column with the sum of all sales.

Last, drop the unneeded columns (the array of month/sales by department).

 

It is pretty well explained on the Agregate results example part of the GroupBy documentation.

 

Theo

Highlighted
Frequent Visitor

Re: Creating a new summarized collection based on a query

Thank you so much, Carlos. 

I created a new Collection and inside of if, I created the column you've mentioned. 

It worked.

 

Highlighted
Frequent Visitor

Re: Creating a new summarized collection based on a query

Thank you.

It's working now and thanks for giving me more examples.

Have a great day.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,577)