Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
iwf
Helper I
Helper I

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?

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

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

 

 

 

2018-11-22_16-46-40.jpg2018-11-22_16-46-16.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

7 REPLIES 7
LivioLanzo
Solution Sage
Solution Sage

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

 

 

 

2018-11-22_16-46-40.jpg2018-11-22_16-46-16.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

PattemManohar
Community Champion
Community Champion

@iwf Please try this as a "New Measure"

 

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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Hi,

 

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

Hello @iwf

which solution is giving you the circular dependency?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

 

I don thave the separate data for Stock. I know, I can create new query and also calculatetable for dates, but I dont wanted to create two additional tables. So I tried the "simplier" solution. Also the formula was easier for me to understand.

Hello @iwf

 

the Stocks and Quartes tables just contain the unique values coming the main table  of Stocks and Querters respectively

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  


@LivioLanzo

 Understand. I am going to try your solution


 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors