Showing results for 
Search instead for 
Did you mean: 
Level: Powered On

Sort gallery based on the most listed item

Hi guys,


I have 3 tables, first one with different instruments (instruments), second one with different sets (sets) and the last one with the 2 primary keys (many-to-many). So a set can contain multiple instruments and an instrument can be in multiple sets.


Additionally I have the column "amount" in my many-to-many table. So a set can contain a specific instrument multiple times. Basically I want to know the total amount of the instruments in sets and display all the instruments including their amount in a gallery sorted descending by the amount- here is an example:


InstrumentID    SetID     Amount

Instrument_A    Set_B     1

Instrument_B    Set_B     2

Instrument_A    Set_A     3

Instrument_C    Set_C     4

Instrument_A    Set_C     2


So the Instrument_A should be listed at first with total amount of 6, Instrument_C second with amount of 4 and Instrument_B third with amount of 2.


Instrument            Amount

Instrument_A                   6

Instrument_C                   4

Instrument_B                   2


Does anyone know how to do it?



Community Support Team
Community Support Team

Re: Sort gallery based on the most listed item

Hi @katunar ,


I think this cannot be achieved currently. Unless you want to create a new collection and in the formula you need to count for each record manually:

ClearCollect(C1, {InstrumentID:"Instrument_A",Amount:Sum(Filter(Table3,InstrumentID="Instrument_A"),Amount)}, 

Then use C1 as the data source of the gallery and sort like this:

SortByColumns( C1, Amount,Descending )






Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Level: Powered On

Re: Sort gallery based on the most listed item

Thanks for your reply @v-monli-msft


I think this won't help because it is a dynamic data source in which new records will be created time by time and I can't change the collection everytime a new instrument is patched.


If there is another way, please let me know.



Helpful resources


Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!


Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020


Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 385 members 5,902 guests
Please welcome our newest community members: