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) )
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
Continue your learning in our online communities.
Features releasing from October 2019 through March 2020
Coming to a city near you
Fill out a quick form to claim your user group badge now!
Connect, share, and learn with your peers year-round