cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Add Calculated Column to Collection

I am collecting a dataset from SQL. This data set is a transaction list with amounts, months, quarter and week number columns,  as well as others. 

 

I would like to summarize this collection to display totals by:
- Week

- Month

- Quarter

- Year

 

I followed this article https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby, but haven't been able to achieve the wanted result. 

 

Here is the code to Collect the underlying data: 

ClearCollect(CustomerSales, 
FirstN(ShowColumns(SortByColumns(Filter('[dbo].[Invoices]',customerid = customer_id, fiscalyear=2019), "txndate", Ascending), 
"totalamount", "fiscalweek", "fiscalmonth"), 24
))

I would like to create a SalesbyWeek, SalesbyMonth, etc to be used in a chart visual. 

5 REPLIES 5
Highlighted
Anonymous
Not applicable

Re: Add Calculated Column to Collection

Hi @AFWright 

 

Ideally you should do the calculation on server side by creating a view or sp and call the same in your app.  This way the app performance would be much better.

 

Thanks.

Highlighted
Community Support
Community Support

Re: Add Calculated Column to Collection

Hi @AFWright ,

 

I have made a test on my side, and succeed to summarize this collection by month.

  • The left table is the original collection created by your formulas that need to summarize.
  • The middle table is the collection that grouped by month, with following formula
ClearCollect(colbymonth, GroupBy(Col,"fiscalmonth","money"))
  • The right table is the collection that contain aggregate results, with following formula.
ClearCollect(colSalesbyMonth,AddColumns(colbymonth,"SalesbyMonth", Sum(money, totalamount)))

Annotation 2019-08-22 193140.png

For more information, please refer to the doc: Aggregate results

 

Best regards,

Sik

Highlighted
Helper II
Helper II

Re: Add Calculated Column to Collection

Thank you for the feedback. I tried it on my dataset, but did not succeed.  

 

Is there a way to make these calculations "on visible"? 

Highlighted
Community Support
Community Support

Re: Add Calculated Column to Collection

Hi @AFWright 

 

Can you share more screenshots and error details of you issue?

It is convenient to use GroupBy and Sum to calculate by groups.

 

if you want calculations on visible, maybe you can combine sum and filter functions to calculate the value in turn.

 

Best regards.

Sik

Highlighted
Helper II
Helper II

Re: Add Calculated Column to Collection

Thank you for the follow up. Interesting problem I'm having here. Desktop/browser version calculates sales for the period accurately using the "on visible" formulas below. Once the app is published and running however, suddenly, it only displays one month and one week, so the numbers are "summarized". 

 

Any suggestions are welcome on how to make this work properly. Thank you.

 

Set(customer_id, CustomerList.Selected.Customer_ID);
ClearCollect(CustomerSales, 
FirstN(ShowColumns(SortByColumns(Filter('[dbo].[Invoices]',customerid = customer_id, fiscalyear=2019), "txndate", Ascending), 
"totalamount", "fiscalweek", "fiscalmonth"), 24
));
ClearCollect(CustomerSalesM, GroupBy(CustomerSales,"fiscalmonth","mamount"));
ClearCollect(CustomerSalesSummM,AddColumns(CustomerSalesM,"SalesbyMonth", Sum(mamount, totalamount)));
ClearCollect(CustomerSalesW, GroupBy(CustomerSales,"fiscalweek","wamount"));
ClearCollect(CustomerSalesSummW,AddColumns(CustomerSalesW,"SalesbyWeek", Sum(wamount, totalamount)));
ClearCollect(CustomerItems, Filter('[dbo].[vAOC_Sales180]', Customer_ID = CustomerList.Selected.Customer_ID) )


 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

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,061)