Forum Discussion

likhithar's avatar
likhithar
Icon for Helper III rankHelper III
4 years ago
Solved

RANKX With Ties for Dynamic TOPN

Hello All,

  I have Meetings Completed to Meetings Assigned  by employee wise, Dividing Meetings Completed/Meetings Assigned gives me the Ratio.

The Ratio is having Ties with multiple employees,means Emp A,B,C,D,E has Ratio as 1 and if give Top 3 from Numeric Range(What if Parameter) it's showing all the Emp's who is having Ratio 1.

I used the following DAX in Power BI

Ratio = SUM(Sheet1[Meetings Completed])/SUM(Sheet1[Meetings Assigned])
RANKX =
CALCULATE([Ratio],
FILTER(VALUES(Sheet1[Emp Name]),
RANKX(ALL(Sheet1[Emp Name]),[Ratio],,DESC)<=SELECTEDVALUE(Parameter[Parameter])))

 

I used RAND function ,it's working but it's Static Scenario,means from Filter Name have to select Bottom 5 ,10 etc for Top Emp.
Rank_NEW =
RANKX(ALL(Sheet1[Emp Name]),[Ratio],,DESC)+RAND()

 

 Here is the Data snap 
The tried the below article also,But didn't met my scebnario

Help me out on this

TIA

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • isabella's avatar
    isabella
    4 years ago

    Hi likhithar 

    You can add index column in power query to solve this issue,here are my steps,you can take a reference：

    Go into the power query

     

    Then sort met ratio field

     

    Then sort emp name field

     

     

    Then add index column from 1 

     

     

    Now the data look as：

     

     

    in visualization，use the index field to create the slicer

     

     

    Best Regards,

    Community Support Team _Isabella

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

5 Replies

    • likhithar's avatar
      likhithar
      Icon for Helper III rankHelper III
      4 years ago

      Hi CNENFRNL 

      Thanks for the Reply..My Requirement is If I select Rank 3, it has to show 3 Employees only who are with 100% in the above scenario(Ex: It might be A,B,C).Is it possible to achieve??

  • isabella's avatar
    isabella
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Hi likhithar 

    You can add index column in power query to solve this issue,here are my steps,you can take a reference：

    Go into the power query

     

    Then sort met ratio field

     

    Then sort emp name field

     

     

    Then add index column from 1 

     

     

    Now the data look as：

     

     

    in visualization，use the index field to create the slicer

     

     

    Best Regards,

    Community Support Team _Isabella

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