Forum Discussion

thangdev's avatar
thangdev
Regular Visitor
2 years ago
Solved

RANKX function - need help explain

Hello Everyone, 

I'm doing the power BI lab, I have ameasure total sale like below: 

Total Sales = SUMX('Order Details', 'Order Details'[Quantity] * 'Order Details'[UnitPrice])

And I would like to use RANKX function to rank the Category by total sale: 

  1. rankx_1 = RANKX(ALLSELECTED(Categories),[Total Sales],,ASC)
  2. rankx_2 = RANKX(ALLSELECTED(Categories),SUMX('Order Details', 'Order Details'[Quantity] * 'Order Details'[UnitPrice]),,ASC)
 
Measure rankx_1 give me an expected result , the other show "1" in all row of the table. 

 

Could you please help explain? 
 
  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi thangdev 
    In the 2nd formula CALCULATE is missing to perform context transition.
    While in the 1st formula you referenced a measure and all measures are automatically wrapped with an implicit CALCULATE therefore, automatically perform context transition.

     

    You can use either of the following

    =
RANKX (
    ALLSELECTED ( Categories ),
    SUMX (
        CALCULATETABLE ( 'Order Details' ),
        'Order Details'[Quantity] * 'Order Details'[UnitPrice]
    ),
    ,
    ASC
)
    =
RANKX (
    ALLSELECTED ( Categories ),
    CALCULATE (
        SUMX ( 'Order Details', 'Order Details'[Quantity] * 'Order Details'[UnitPrice] )
    ),
    ,
    ASC
)

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    Hi thangdev 
    In the 2nd formula CALCULATE is missing to perform context transition.
    While in the 1st formula you referenced a measure and all measures are automatically wrapped with an implicit CALCULATE therefore, automatically perform context transition.

     

    You can use either of the following

    =
RANKX (
    ALLSELECTED ( Categories ),
    SUMX (
        CALCULATETABLE ( 'Order Details' ),
        'Order Details'[Quantity] * 'Order Details'[UnitPrice]
    ),
    ,
    ASC
)
    =
RANKX (
    ALLSELECTED ( Categories ),
    CALCULATE (
        SUMX ( 'Order Details', 'Order Details'[Quantity] * 'Order Details'[UnitPrice] )
    ),
    ,
    ASC
)