cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
plzampieri
Level: Powered On

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
PowerApps Staff CarlosFigueira
PowerApps Staff

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
PowerApps Staff CarlosFigueira
PowerApps Staff

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

tchin-nin
Level 10

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

plzampieri
Level: Powered On

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.

 

plzampieri
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,552)