cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OsmandFernando
Post Prodigy
Post Prodigy

Further on Collection

Hi @PowerAddict and other experts, 

 

I have done a collection using the following formula and it looks ok

 

ClearCollect(SalesbyLocation,ShowColumns(SalesTable,"SalesYear","SalesMonth","Customer_x0020_Name_x003a_Root_x","Total_x0020_Invoice_x0020_Value"));

 

Following is how it looks like, the Root comes as a group with ID and Value. 

 

Following are my requirements, 

 

1. How can I get the Customer Name root..... with value only to my current collection

2. How can I create a collection to show the Total Invoice Value by Location (total must be summarized by location)

 

Capture.PNG

 

Capture1.PNG

 

Thank you

Osmand

1 ACCEPTED SOLUTION

Accepted Solutions
PowerAddict
Super User
Super User

The answer to your first question should be the AddColumns function. So something like this might help:

ClearCollect(SalesbyLocation,ShowColumns(AddColumns(SalesTable,"CustomerName", Customer_x0020_Name_x003a_Root_x.Value), "SalesYear","SalesMonth","CustomerName","Total_x0020_Invoice_x0020_Value"));

Let me know if this doesn't work. The answer to your question is the GroupBy function. I will try to pit an example together as soon as I get in front of a computer.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

View solution in original post

4 REPLIES 4
PowerAddict
Super User
Super User

The answer to your first question should be the AddColumns function. So something like this might help:

ClearCollect(SalesbyLocation,ShowColumns(AddColumns(SalesTable,"CustomerName", Customer_x0020_Name_x003a_Root_x.Value), "SalesYear","SalesMonth","CustomerName","Total_x0020_Invoice_x0020_Value"));

Let me know if this doesn't work. The answer to your question is the GroupBy function. I will try to pit an example together as soon as I get in front of a computer.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

Were you able to solve this? If not, let me know what questions you have and I can try to solve them now.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://www.thepoweraddict.com

Thank you @PowerAddict your direction is helping me lot. 

 

I was able to find an answer to the 2nd question by my own, i applied the following and it worked

 

ClearCollect(SalesSumbyLocation,AddColumns(GroupBy(SalesbyLocation,"CustomerLocation","Grouped"),"TotalSales",Sum(Grouped,Total_x0020_Invoice_x0020_Value)));
 
Osmand

@OsmandFernando that's great work! Good luck with the rest of the app! Feel free to reach out in case of any questions!

Thanks,
Hardit Bhatia
https://thepoweraddict.com

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,273)