Rank in Direct Query to sort

[harshagraj]

Hello all, 
I am in direct query mode and i am trying to achieve a horizontal bar chart with highest rated user in the module like below. I am able to this in Tableau and I am not able to create a rank calculation in Power BI. Kinldy modify the below dax to achieve like the ss below.

Manager Rating is a calculated measure 

Manager Rating =

CALCULATE (

    SUM ( 'USER_RATING_VIEW'[MANAGER_RATINGS_FINAL] ),

    ALLEXCEPT (

        'USER_RATING_VIEW',

        'USER_RATING_VIEW'[USER_DN],

        'USER_RATING_VIEW'[Module],

        'USER_RATING_VIEW'[QUESTION],

        'USER_RATING_VIEW'[MANAGER_RATINGS_FINAL],

        'USER_RATING_VIEW'[JOB_ROLE_BAND]

    )

)

Required view on Power BI

Tried this below rank to sort.

Rank_Module =

RANKX(

    CALCULATETABLE(

        VALUES(USER_RATING_VIEW[USER_DN]),

        ALLSELECTED(USER_RATING_VIEW[USER_DN])),

CALCULATE([Manager Rating]))

Module	Manager Rating	USER_DN
R&R	162	C
SOI	126	C
KSI	114	C
R&R	108	A
DT	105	C
R&R	73	D
KSI	66	A
DT	63	A
DT	63	D
SOI	60	A
SOI	56	D
R&R	48	B
KSI	45	D
SOI	30	B
KSI	29	B
DT	28	B

[Anonymous · Accepted solution]

Hi @harshagraj ,

As far as I know, Power BI doesn't support us to sort values in same legend with different groups at the same time.

Here I have a workaround. You need to add [USER_DN] in Y-axis as level2 and add Rank measure into Tooltip.

Measure:

Rank_Module = 
MAX(USER_RATING_VIEW[Module])
&""&
RANKX(
    CALCULATETABLE(
        VALUES(USER_RATING_VIEW[USER_DN]),
        ALLSELECTED(USER_RATING_VIEW[USER_DN])),
CALCULATE([Manager Rating]))
 

Sort axis by [Rank_Module] measure. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.