Forum Discussion

iwf's avatar
iwf
Helper I
7 years ago
Solved

RANK by Sumamrized data

Hello,

I have this kind of data:

STOCK                   PERIOD                 VOLUME

AAA                       Q1                          150

AAA                       Q2                          2000

AAA                       Q3                          0

AAA                       Q4                          50

BBB                        Q1                          300

BBB                        Q2                          250

BBB                        Q3                          15

BBB                        Q4                          1000

CCC                        Q1                          200        

CCC                        Q2                          600

CCC                        Q3                          85

CCC                        Q4                          140

 

 

On the report I have time slicer. Generally I want to choose for example Q2-Q4 and want DAX (RANKX) to SUM Volumes of Stock in selected period and give me Rank of the stocks.

Any idea how to solve this?

Need Help
Tips and Tricks
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

      Hi iwf

     

    try :

     

     

    Ranking = 
IF(
    AND( COUNTROWS( Data ) > 0, HASONEVALUE( Stocks[STOCK] ) ),
    RANKX(
        CALCULATETABLE(
            SUMMARIZE( Data, Stocks[STOCK], Quarters[PERIOD] ),
            ALLSELECTED()
        ),
        CALCULATE( SUM( Data[VOLUME] ) )
    )
)

     

     

     

7 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    iwf Please try this as a "New Measure"

     

    Test150Rnk = RANKX(ALLSELECTED(Test150DynRnk),CALCULATE(SUM(Test150DynRnk[Volume])),,DESC,Dense)

    • iwf's avatar
      iwf
      Helper I
      7 years ago

      Hi,

       

      thank you for advice. But it gives me error: A circular dependency was detected: Tradelog(Ranktest). Dont know why

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

      Hi iwf

     

    try :

     

     

    Ranking = 
IF(
    AND( COUNTROWS( Data ) > 0, HASONEVALUE( Stocks[STOCK] ) ),
    RANKX(
        CALCULATETABLE(
            SUMMARIZE( Data, Stocks[STOCK], Quarters[PERIOD] ),
            ALLSELECTED()
        ),
        CALCULATE( SUM( Data[VOLUME] ) )
    )
)