Adding SORT or SORTBY into excel function - Stack Overflow

admin管理员组

文章数量:1400026

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)

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)

• excel
• excel-formula
• array-formulas
• dynamic-arrays

Share Improve this question Follow asked Mar 25 at 21:08 Matt9080Matt9080 4555 bronze badges 3

• 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 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 Commented Mar 26 at 12:38
• Replace the leftmost Revenue with SORTBY(Revenue,Revenue[FY24 Revenue],-1). – VBasic2008 Commented Mar 26 at 12:46

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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.

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

本文标签： Adding SORT or SORTBY into excel functionStack Overflow