Adding SORT or SORTBY into excel function

Question

I am looking to add a sortby or sort function into my query but can't figure out how.

=TAKE(CHOOSECOLS(FILTER(Revenue,(Revenue[Top Product]=$C$66)*(Revenue[Location]=$C$67)),1,2,17),10)

Here is what I would like to add to sort the results descending by total revenue:

SORTBY(Revenue,Revenue[FY24 Revenue],-1)

It should be easy could you post some sample data. In the CHOOSECOLS() resulting which is the Revenue[FY24 Revenue] col could you let us know as well. — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Mar 25 at 21:10
Are you trying to sort the values before you TAKE the first 10 rows, or after you TAKE the first 10 rows? These can each give very different results, and will therefore change whether SORTBY goes inside the TAKE, or outside the TAKE (which would also require you to TAKE(Revenue[FY24 Revenue],10) for your sortby). Really, you might want to look into using a LET to break things down into clearer steps too. — Chronocidal
– Chronocidal, Commented Mar 26 at 12:38
Replace the leftmost Revenue with SORTBY(Revenue,Revenue[FY24 Revenue],-1). — VBasic2008
– VBasic2008, Commented Mar 26 at 12:46

saskiaclr · Accepted Answer · 2025-03-26 13:39:55Z

0

It would really help to see some examples of the data but this is what I would recommend based on my understanding of the issue:

=SORTBY(TAKE(CHOOSECOLS(FILTER(Revenue, (Revenue[Top Product]=$C$66)*(Revenue[Location]=$C$67)), 1, 2, 17), 10), TAKE(FILTER(Revenue[FY24 Revenue], (Revenue[Top Product]=$C$66)*(Revenue[Location]=$C$67)), 10), -1)

I think that should work but again it's hard to say because I can't see the data and am not entirely sure I've understood what you are wanting to do.

edited Mar 26 at 13:39

answered Mar 26 at 10:30

saskiaclr

611 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chronocidal · Accepted Answer · 2025-03-26 14:00:40Z

If you want to Sort the Revenue before you TAKE the first 10 items:

=LET(rev_filter, --(Revenue[Top Product]=$C$66)*(Revenue[Location]=$C$67)), 
     rev_column, HSTACK(CHOOSECOLS(Revenue, 1, 2, 17), Revenue[FY24 Revenue]), 
     rev_filter, FILTER(rev_column, ref_filter>0), 
     rev_sorted, SORT(rev_filter, 4, -1),
     TAKE(rev_sorted, 10, 3)
)

If you want to Sort the Revenue after you TAKE the first 10 items:

=LET(rev_filter, --(Revenue[Top Product]=$C$66)*(Revenue[Location]=$C$67)), 
     rev_column, HSTACK(CHOOSECOLS(Revenue, 1, 2, 17), Revenue[FY24 Revenue]), 
     rev_filter, FILTER(rev_column, ref_filter>0), 
     rev_sorted, SORT(TAKE(rev_filter, 10), 4, -1),
     DROP(rev_sorted, , -1)
)

Note that an important step here is to include the Sort column in the data that you FILTER, so that it all still matches up, and then discard it before returning the results.

Why 2 versions? Consider the following table:

Value1	Value 2
A	1
B	4
C	2
D	3

If you TAKE the top 3 rows, and then sort it on Value 2, you get this result:

Value1	Value 2
A	1
C	2
B	4

However, if you sort it on Value 2, and then TAKE the top 3 rows, you get this result instead:

Value1	Value 2
A	1
C	2
D	3

Collectives™ on Stack Overflow

Adding SORT or SORTBY into excel function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related