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:
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.
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.
Hi @AFWright ，
I have made a test on my side, and succeed to summarize this collection by month.
ClearCollect(colSalesbyMonth,AddColumns(colbymonth,"SalesbyMonth", Sum(money, totalamount)))
For more information, please refer to the doc: Aggregate results
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.
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) )
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.
We are excited for the next Super User season.
FIll out a quick form to claim your community user group member badge today!
Features releasing from October 2020 through March 2021