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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
thangdev
Regular Visitor

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. 
thangdev_0-1696672354731.png

 

Could you please help explain? 
 
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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
)

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

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
)

hi @tamerj1 ,

thank you much for your explanation. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

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