SOLVED

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

Iron Contributor

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 result

I have added an image and the link of the samplefile below

Hope for your help

Thank you

small_village_0-1658896654861.png

https://docs.google.com/spreadsheets/d/1dguv1x_7GNZy47cijx9LFPqCljah5C5v/edit?usp=sharing&ouid=11231... 

5 Replies

@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))
best response confirmed by littlevillage (Iron Contributor)
Solution

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 

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

image.png

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

@Harun24HR 

Thank you for your help

It works correctly

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
1 best response

Accepted Solutions
best response confirmed by littlevillage (Iron Contributor)
Solution

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})

View solution in original post