RANKX function - need help explain

[thangdev]

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 · Accepted solution]

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
)

[thangdev]

hi @tamerj1 ,

thank you much for your explanation.