Unanswered

Add Calculated Column to Collection

(0) Share

Report

Posted on by AFWright

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report
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)))
For more information, please refer to the doc: Aggregate results

Best regards,
Sik

Was this reply helpful? Yes No
AFWright 87 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
AFWright 87 on at

Like (0)

Report
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) )

Was this reply helpful? Yes No