topic RANKX function - need help explain in DAX Commands and Tips

RANKX function - need help explain

thangdev — Sat, 07 Oct 2023 09:56:54 GMT

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:

rankx_1 = RANKX(ALLSELECTED(Categories),[Total Sales],,ASC)
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?

Re: RANKX function - need help explain

tamerj1 — Sat, 07 Oct 2023 10:08:52 GMT

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 )

Re: RANKX function - need help explain

thangdev — Sat, 07 Oct 2023 13:28:34 GMT

hi tamerj1 ,

thank you much for your explanation.