SOLVED

Contributor

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

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

Thank you

5 Replies

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

@small_village 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 small_village (Contributor)
Solution

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

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

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

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
)
ADDCOLUMNS ( getTop, "Index", RANKX ( getTop, [Percent],, DESC ) )
SELECTCOLUMNS (
"Serial", [Index],
"Code ID", [Bưu cục gửi hàng],
"Late", [Late],
"Percentage", [Percent]
)
RETURN
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.

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

It works correctly

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

How do you sort a table from highest to lowest?
Sort the table
Select Custom Sort.