Jul 26 2022 09:38 PM
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
Jul 26 2022 09:59 PM
@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))
Jul 26 2022 11:52 PM - edited Jul 26 2022 11:55 PM
SolutionYou 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})
Jul 27 2022 07:30 AM
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.
Jul 27 2022 04:36 PM
Jul 27 2022 11:51 PM
Jul 26 2022 11:52 PM - edited Jul 26 2022 11:55 PM
SolutionYou 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})