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

Toplist of orders by customers - how to create a collection on app-start?

I want to create a collection on app-start contianing the top 5 customers with most orders based on my order-table.

 

order table have column OrderName and Lookup column Customer (lookup to table Customers)

 

I can do this: FirstN(Distinct(Order-Table;Customer);5)
If i put this in a gallery i get the 5 rows... but i want the top 5 with the most orders and i want to Sort the table so the one with the most orders are at the top.

 

How can i do this?

 

1 ACCEPTED SOLUTION

Accepted Solutions
BCBuizer
Super User
Super User

Hi @Oskarkuus ,

 

Indeed this will not work for a LookUp column as such, so another column can be added that has the customer.value:

 

 

FirstN(
    Sort(
        AddColumns(
            GroupBy(
                AddColumns(
                    Order-Table;
                    "_Customer";
                    Customer.Value
                ),
                "_Customer";
                "CustomerGroups"
            ),
            "Count";
            CountRows(CustomerGroups)
        );
        Count;
        Descending
    );
    5
)

 

View solution in original post

3 REPLIES 3
BCBuizer
Super User
Super User

Hi @Oskarkuus,

 

I assume your order table just has order items in it and no count of orders.

 

The below creates a table that has the distinct Customer names and Count of orders in your order-table. This is then sorted by count and finally only the first 5 are displayed:

 

FirstN(
	Sort(
		AddColumns(
			Distinct(
				Order-Table;
				Customer
			);
			"Count";
			CountRows(
				GroupBy(
					Order-table;
					"Customer";
					"Temp"
				)
			)
		); 
	Count
	); 
	5
)

 

I dont really get it.

Oskarkuus_0-1647942943721.png

 

These columns i dont understand how to adapt to my tables.

Since Customer is a lookup column i dont think i can use that in that sense , correct?

 

 

BCBuizer
Super User
Super User

Hi @Oskarkuus ,

 

Indeed this will not work for a LookUp column as such, so another column can be added that has the customer.value:

 

 

FirstN(
    Sort(
        AddColumns(
            GroupBy(
                AddColumns(
                    Order-Table;
                    "_Customer";
                    Customer.Value
                ),
                "_Customer";
                "CustomerGroups"
            ),
            "Count";
            CountRows(CustomerGroups)
        );
        Count;
        Descending
    );
    5
)

 

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.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,653)