Forum Discussion
littlevillage
Jul 27, 2022Iron 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 ...
- 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})
SergeiBaklan
Jul 27, 2022Diamond Contributor
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.