RANKX With Ties for Dynamic TOPN

[likhithar]

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

https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

Help me out on this

TIA

[CNENFRNL · Accepted solution]

[isabella · Accepted solution]

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.

[CNENFRNL]

[likhithar]

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??

[CNENFRNL · Accepted solution]

[likhithar]

@CNENFRNL It worked perfectly!!!Thank You 🙂