cancel
Showing results 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

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

Proud to be a Datanaut!

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

Proud to be a Datanaut!

Community Champion

@iwf Please try this as a "New Measure"

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

Proud to be a PBI Community Champion

Helper I

Hi,

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

Solution Sage

Hello @iwf

which solution is giving you the circular dependency?

Proud to be a Datanaut!

Helper I
`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.

Solution Sage

Hello @iwf

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

Proud to be a Datanaut!

Helper I

@LivioLanzo

Understand. I am going to try your solution

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.