cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Dynamic Rank Measure

Hi,

I have a query that brings in all stock symbols traded each day by shares traded.  So the dataset looks like this but its shows by day for the last 13 months (there are millions of rows of data).  What I would like to do is show the top 10 stocks by quantity by day represented in a table (see second table below)

 Date Symbol Description Shares 2/1/2021 XYZ XYZ Stock 10,0000 2/1/2021 ABC ABC Stock 20,0000 2/1/2021 TYT TYT STock 30,0000 2/1/2021 GF GF Stock 40,0000 2/1/2021 TFT TFT Stock 50,0000

I would like to represent the top 10 each day like this showing the top 10 symbols for each day and the shares traded (also the rank if possible)

 2/1 2/2 2/3 Symbol Shares Rank Symbol Shares Rank Symbol Shares Rank ABC 10 1 HGSJ 78 1 DH 99 1 ABD 9 2 FSD 55 2 SD 52 2 ABDE 8 3 FS 21 3 DS 33 3 HJH 7 4 TT 4 4 ED 5 4 IOP 6 5 FDS 4 5 G 1 5

Any help would be greatly appreciated.

Thanks,

Kevin

1 ACCEPTED SOLUTION
Community Support

Hi, @kjmts5200

According to your description, I think you need to create a rank column first, then use it in viusal and visual filter pane to filter top 10, then create a measure to display corresonding symbol names.

Like this:

``````rank =
RANKX (
FILTER ( Table1, [Date] = EARLIER ( Table1[Date] ) ),
RANKX ( Table1, Table1[Shares],, ASC, SKIP ) * 100
+ RANKX ( Table1, [Symbol],, DESC, SKIP )
)``````
``````symbol1 =
MAXX (
FILTER (
ALL ( Table1 ),
[Date] = SELECTEDVALUE ( Table1[Date] )
&& [rank] = SELECTEDVALUE ( Table1[rank] )
),
[Symbol]
)``````

Here is my sample file. Hope it helps.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Resident Rockstar

I am not sure how you would do it on daily level as there are millions of rows of data...I did have a try, say you get a disconnected table with only one column to display all the rank you need, 1-10 or 1-5, then return the Symbol and Shares with measures, assume [TOTAL] is a measure to sum up your shares

``````SharesResult =
VAR CurDate =
SELECTEDVALUE ( 'Table'[Date] )
VAR CurRank =
SELECTEDVALUE ( Ranking[Rank] )
VAR T1 =
FILTER ( 'Table', 'Table'[Date] = CurDate )
RETURN
MINX ( TOPN ( CurRank, T1, [TOTAL], DESC ), [TOTAL] )``````

``````SymbolResult =
VAR CurDate =
SELECTEDVALUE ( 'Table'[Date] )
VAR CurRank =
SELECTEDVALUE ( Ranking[Rank] )
VAR T1 =
GROUPBY (
FILTER ( 'Table', 'Table'[Date] = CurDate ),
'Table'[Symbol],
"SUM", SUMX ( CURRENTGROUP (), 'Table'[Shares] )
)
VAR T2 =
ADDCOLUMNS ( T1, "RANKING", RANKX ( T1, [SUM],, DESC ) )
RETURN
MINX ( FILTER ( T2, [RANKING] = CurRank ), [Symbol] )``````
Community Support

Hi, @kjmts5200

According to your description, I think you need to create a rank column first, then use it in viusal and visual filter pane to filter top 10, then create a measure to display corresonding symbol names.

Like this:

``````rank =
RANKX (
FILTER ( Table1, [Date] = EARLIER ( Table1[Date] ) ),
RANKX ( Table1, Table1[Shares],, ASC, SKIP ) * 100
+ RANKX ( Table1, [Symbol],, DESC, SKIP )
)``````
``````symbol1 =
MAXX (
FILTER (
ALL ( Table1 ),
[Date] = SELECTEDVALUE ( Table1[Date] )
&& [rank] = SELECTEDVALUE ( Table1[rank] )
),
[Symbol]
)``````

Here is my sample file. Hope it helps.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thank you Janey.  The rank function was not providing accurate info but i was able to update to below.  The symbol1 idea works great and will help with many other projects.  Thank you so much for the help

``````rank =
RANKX (
FILTER ( Table1, [Date] = EARLIER ( Table1[Date] ) ),Table1, [Shares])``````