Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kjmts5200
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)

 

DateSymbolDescriptionShares
2/1/2021XYZXYZ Stock10,0000
2/1/2021ABCABC Stock20,0000
2/1/2021TYTTYT STock30,0000
2/1/2021GFGF Stock40,0000
2/1/2021TFTTFT Stock50,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  
SymbolSharesRankSymbolSharesRankSymbolSharesRank
ABC101HGSJ781DH991
ABD92FSD552SD522
ABDE83FS213DS333
HJH74TT44ED54
IOP65FDS45G15

 

Any help would be greatly appreciated.

 

Thanks,


Kevin

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

1.png

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.

View solution in original post

3 REPLIES 3
Vera_33
Resident Rockstar
Resident Rockstar

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

1.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors