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

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
shailendra74
Level 10

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.

v-siky-msft
Level 10

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

AFWright
Level: Powered On

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"? 

v-siky-msft
Level 10

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

AFWright
Level: Powered On

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