Forum Discussion

harshagraj's avatar
harshagraj
Post Partisan
3 years ago
Solved

Rank in Direct Query to sort

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]))
 
 

 

 

ModuleManager RatingUSER_DN
R&R162C
SOI126C
KSI114C
R&R108A
DT105C
R&R73D
KSI66A
DT63A
DT63D
SOI60A
SOI56D
R&R48B
KSI45D
SOI30B
KSI29B
DT28B
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.