Forum Discussion

kjmts5200's avatar
kjmts5200
Frequent Visitor
5 years ago
Solved

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 da...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-janeyg-msft
Community Support
5 years ago

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's avatar
    kjmts5200
    Frequent Visitor
    5 years ago

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