How to fetch record on specific calculation from the list of records ?

[dips23]

Hello,

I have list of records as follows in datasource:

Item         Text        Num

Item 1	Text 1	3
Item 2	Text 2	2
Item 2	Text 3	4
Item 1	Text 4	6

I want to perform following operations using formula:

 1. Group by Items:

Item 1	Text 1	3
Item 1	Text 4	6
Item 2	Text 2	2
Item 2	Text 3	4

 2. sum Num of grouped item

Item 1	Text 1	3
Item 1	Text 4	6
		sum= 9
Item 2	Text 2	2
Item 2	Text 3	4
		sum=6

3. Sort by highest value of  sum of num 

Item        Num Sum

Item 1     9

Item 2     6

How can I bind the resulted values as indicated in 3. to list control ? ( Basically, I need distrinct records from the datasource sorted by highest value of thier numbers )

I could do group with ClearCollect, but I am stucked at step 2. and 3. - Any suggestions ?

Best Regards,

[RandyHayes · Accepted solution]

@dips23 

You mention "list control".  Are you referring to a Gallery?  Or are you talking about a List box?

If you're looking at a Gallery type control, You can set the Items property of your control to the following:

SortByColumns(
   AddColumns(
      GroupBy(yourDataSource, "Item", "Items"), 
      "ItemSum", Sum(Items,Num)), 
   "ItemSum",Descending)

This will group the records and create a sum of each num within the distinct item.

I've attached a sample app with this logic for you to review.

I hope it is helpful for you.

[dips23]

Thank you so much for your time, It did workout for my datasource exactly the way I wanted 🙂 ! You are Awesome!

Best Regards,