Hi,I am trying to get top 10 from a table and sorted from highest to lowest.I have written a formula:=SORT(FILTER(FILTER(B5:E125,C5:C125>=LARGE(C5:C125,10),""),{1,1,0,1},""),3,-1)The formula has shown top 13, It is not the same my expect resultI have added an image and the link of the samplefile belowHope for your helpThank youhttps://docs.google.com/spreadsheets/d/1dguv1x_7GNZy47cijx9LFPqCljah5C5v/edit?usp=sharing&ouid=112310544159929632856&rtpof=true&sd=true

You do not need FILTER() twice. You can choose columns from INDEX(). Try-=INDEX(SORT(FILTER(B5:E125,C5:C125>=LARGE(C5:C125,10)),4,-1),SEQUENCE(10),{1,2,4})

How can I get top 10 from a table and sorted from highest to lowest.

5 Replies

Willjoe2442
Brass Contributor
Jul 28, 2022
How do you sort a table from highest to lowest?
Sort the table
Select Custom Sort.
Select Add Level.
For Column, select the column you want to Sort by from the drop-down, and then select the second column you Than by want to sort.
For Sort On, select Values.
For Order, select an option, like A to Z, Smallest to Largest, or Largest to Smallest.

Regards,
Will

SergeiBaklan

Diamond Contributor

Jul 27, 2022

littlevillage

You may do that directly from data model using linked back table.

1) Create any dummy table

2) Add it to data model

3) Data -> Existing connections - select above table -> open -> save as table into the sheet

4) Right click menu on it -> Table -> Edit DAX -> select DAX as Command type -> add DAX query

EVALUATE
VAR getTop =
    TOPN (
        10,
        TOPN (
            10,
            SUMMARIZECOLUMNS (
                Data[Bưu cục gửi hàng],
                KEEPFILTERS (
                    FILTER (
                        ALL ( Data[Bưu cục gửi hàng] ),
                        NOT ( ISBLANK ( Data[Bưu cục gửi hàng] ) )
                    )
                ),
                "Late", [PU trễ giờ],
                "Percent",
                    [Tỷ lệ PU trễ giờ] + [PU trễ giờ] / 1e8
                        + RANDBETWEEN ( 1, 10000 ) / 1E18
            ),
            [Late], DESC
        ),
        [Percent], DESC
    )
VAR addIndex =
    ADDCOLUMNS ( getTop, "Index", RANKX ( getTop, [Percent],, DESC ) )
VAR adjustColumns =
    SELECTCOLUMNS (
        addIndex,
        "Serial", [Index],
        "Code ID", [Bưu cục gửi hàng],
        "Late", [Late],
        "Percentage", [Percent]
    )
RETURN
    adjustColumns
ORDER BY
    [Serial] ASC,
    [Late] DESC

5) Design table as desired

Serial number is generate with RANKX with some assumptions about values ranges. Perhaps we could take Total instead of Late, not sure about content.

Samplefile (1).xlsx14.5 MB

Harun24HR
Bronze Contributor
Jul 27, 2022
You do not need FILTER() twice. You can choose columns from INDEX(). Try-

=INDEX(SORT(FILTER(B5:E125,C5:C125>=LARGE(C5:C125,10)),4,-1),SEQUENCE(10),{1,2,4})
- littlevillage
  Iron Contributor
  Jul 27, 2022
  Harun24HR
  Thank you for your help
  It works correctly
Riny_van_Eekelen
Platinum Contributor
Jul 27, 2022
littlevillage Wrap your formula in an INDEX function like this:
=INDEX(SORT(FILTER(FILTER(B5:E125,C5:C125>=LARGE(C5:C125,10),""),{1,1,0,1},""),3,-1),SEQUENCE(10),SEQUENCE(,3))

Forum Discussion

How can I get top 10 from a table and sorted from highest to lowest.

5 Replies

Resources