Forum Discussion

vivekn280393's avatar
vivekn280393
New Member
2 years 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...
Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi vivekn280393 -Can you please upload pbix file in drive , it helps to analyse. 

    Thank you

  • Anonymous's avatar
    Anonymous
    2 years 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.

Anonymous's avatar
Anonymous
Not applicable
2 years 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.