Dynamic Rank Measure

[kjmts5200]

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

[Anonymous]

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] )

[v-janeyg-msft · Accepted solution]

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.

[kjmts5200]

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])