cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fmc1
Helper I
Helper I

Sort Collection By Column From and AddColumn

Have a table with three columns: Market, Order Qty and Unit Price. What I want to do is sort by total sales per market in descending order. I have created the following:

ClearCollect(mySalesData,AddColumns(SalesData,"Sale Amount",'Unit Price'*'Order Qty'));
ClearCollect(myMarkets,GroupBy(mySalesData,"Market","Market Data"));
ClearCollect(myMarketSales,Sort(AddColumns(myMarkets,"Total Sales",Text(Sum('Market Data','Sale Amount'),"###,###.0#"),"Avg Sale",Text(Average('Market Data','Sale Amount'),"###,###.#0")),'Total Sales',Descending));

The above produces the correct data but I cannot get the sort to work; syntactically Power Apps accepts the code. I think the issue is with having a Totals Sales and Avg Sale column. The following works:

ClearCollect(myMarketSalesX,SortByColumns(AddColumns(myMarkets,"Total Sales",Sum('Market Data','Sale Amount')),"Total Sales",Descending));

Appreciate any guidance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@fmc1 

Your issue is (among a couple) that you are sorting text and expecting to be sorted as a number.  This will not work.

From a Text standpoint a sequence of numbers from 1 to 20 is sorted to the following:

1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 3, 4, 5, 6, 7, 8, 9

 

You need to not sort on the text version of your numbers.

 

I'd also say you have way too many collections to produce what you want.  You do realize that each collection is going to hold duplicates of all your data?!  So, you are impacting the performance and memory usage in your app unnecessarily. 

 

You formula on your Gallery Items property can simply be this:

Sort(
    AddColumns(
        GroupBy(
            AddColumns(SalesData,
                "Sale Amount", 'Unit Price' * 'Order Qty'
            ),
            "Market", "Market Data"
        ),
        "_saleTotal", Sum('Market Data','Sale Amount'),
        "_avgSale", Average('Market Data','Sale Amount')
    )
    _saleTotal, Descending
)

This would produce the sorting as you want.

 

Personally (as a design rule) I would put the conversion from Number to Text in your Label or whatever you are displaying the number in.  Technically formatting of the text is an "interface" aspect, not a "data" aspect.

 

BUT, if you still wanted the formatting in the table, then you could change the formula above to:

AddColumns(
  Sort(
    AddColumns(
        GroupBy(
            AddColumns(SalesData,
                "Sale Amount", 'Unit Price' * 'Order Qty'
            ),
            "Market", "Market Data"
        ),
        "_saleTotal", Sum('Market Data','Sale Amount'),
        "_avgSale", Average('Market Data','Sale Amount')
    )
    _saleTotal, Descending
  ),
  "Total Sales", Text(_saleTotal,"###,###.0#"),
  "Avg Sale", Text(_avgSale,"###,###.#0")
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

fmc1
Helper I
Helper I

I was aware of the many collections but was trying to break it down into logical pieces to better understand. I totally missed the formatting breaking the sort, which is something I knew. Again, appreciate the great answer!

View solution in original post

5 REPLIES 5
AJ_Z
Super User
Super User

Are there any error messages?
Also what happens if you sort it in a gallery after you collect it does it react differently? 

e.g. 

SortByColumns(myMarketSalesX,"Total Sales",Descending);



Signature:


If you appreciated my comments/responses please be sure to Like/Kudo them it really does make me smile 🙂 !
Link to the Power Platform Professionals United Kingdom User Group:

https://powerusers.microsoft.com/t5/Power-Platform-Professionals/gh-p/PowerPlatformProfessionalsUnit...

fmc1
Helper I
Helper I

There are no errors (red/orange triangles).

There is no difference if sort the data in a Gallery. I also noticed when I view the collections they are also not sorted. 

RandyHayes
Super User
Super User

@fmc1 

Your issue is (among a couple) that you are sorting text and expecting to be sorted as a number.  This will not work.

From a Text standpoint a sequence of numbers from 1 to 20 is sorted to the following:

1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 3, 4, 5, 6, 7, 8, 9

 

You need to not sort on the text version of your numbers.

 

I'd also say you have way too many collections to produce what you want.  You do realize that each collection is going to hold duplicates of all your data?!  So, you are impacting the performance and memory usage in your app unnecessarily. 

 

You formula on your Gallery Items property can simply be this:

Sort(
    AddColumns(
        GroupBy(
            AddColumns(SalesData,
                "Sale Amount", 'Unit Price' * 'Order Qty'
            ),
            "Market", "Market Data"
        ),
        "_saleTotal", Sum('Market Data','Sale Amount'),
        "_avgSale", Average('Market Data','Sale Amount')
    )
    _saleTotal, Descending
)

This would produce the sorting as you want.

 

Personally (as a design rule) I would put the conversion from Number to Text in your Label or whatever you are displaying the number in.  Technically formatting of the text is an "interface" aspect, not a "data" aspect.

 

BUT, if you still wanted the formatting in the table, then you could change the formula above to:

AddColumns(
  Sort(
    AddColumns(
        GroupBy(
            AddColumns(SalesData,
                "Sale Amount", 'Unit Price' * 'Order Qty'
            ),
            "Market", "Market Data"
        ),
        "_saleTotal", Sum('Market Data','Sale Amount'),
        "_avgSale", Average('Market Data','Sale Amount')
    )
    _saleTotal, Descending
  ),
  "Total Sales", Text(_saleTotal,"###,###.0#"),
  "Avg Sale", Text(_avgSale,"###,###.#0")
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
fmc1
Helper I
Helper I

I was aware of the many collections but was trying to break it down into logical pieces to better understand. I totally missed the formatting breaking the sort, which is something I knew. Again, appreciate the great answer!

RandyHayes
Super User
Super User

@fmc1 

Yes, the many collections are all just duplicates of data in your app.  Best to avoid as much as possible.

If you have to get to a end point by doing so, so be it, but then go back and refactor.

 

Ex.

ClearCollect(colA, Filter(source, Col="A"));
ClearCollect(colB, AddColumns(colA, "someCol", true));
ClearCollect(colC, GroupBy(colB, "ColX", "_records"));
ClearCollect(colD, Sort(colC, ColX));

This becomes 4 copies of the same data.

 

Refactor to:

ClearCollect(colBetter,
    Sort(
        GroupBy(
            AddColumns(
                Filter(source, Col="A"),
                "someCol", true
            ),
            "ColX", "_records"
        ),
        ColX
    )
)

 Only 1 table of data then!

 

Glad it all helped out.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (4,674)