Forum Discussion

vivekn280393's avatar
vivekn280393
New Member
1 year ago
Solved

Regarding ranking using dax

Hi All,

 

I got a requirement to find the top 2nd most earning employee name from each department (department name). I tried using the window rank function but i am not getting the right results. I am attaching the pbix file so can someone help me to fix the issue.
 
Measure i created is 
Window Rank = RANK(DENSE,ALL(Employee),ORDERBY(Employee[Total CTC],DESC),,PARTITIONBY(Employee[Department Name]))
 
Screenshot:

 

 
Regards
Vivek N
 
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi vivekn280393 ,

     

    I suggest you to try RANKX() Function to create a measure.

    Rank by Department = 
RANKX(ALLEXCEPT('Table','Table'[Department Name]),CALCULATE(SUM('Table'[EmpCTC])),,DESC,Dense)

    If you only want to show Top 2nd  based on Department Name in your visualization, you can try to add this rank measure into visual level filter and set it to show items when value <=2.

     

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi vivekn280393 ,

     

    I suggest you to try RANKX() Function to create a measure.

    Rank by Department = 
RANKX(ALLEXCEPT('Table','Table'[Department Name]),CALCULATE(SUM('Table'[EmpCTC])),,DESC,Dense)

    If you only want to show Top 2nd  based on Department Name in your visualization, you can try to add this rank measure into visual level filter and set it to show items when value <=2.

     

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.