Forum Discussion
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
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.
If it doesn’t solve your problem, please feel free to ask me.
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
Hi kjmts5200
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] )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.
If it doesn’t solve your problem, please feel free to ask me.
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.
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])
