RankX - questions - Rank based on measure from another table

[IPGeorgiev]

Hi All,

I would like to ask for some help and will really appreciate it!

Basically I have one table(Table 1) which has the following columns

• employee names
• Supervisor name

I have another table (Table 2) which has raw data for each employee and the following columns:

• Employee name
• Date
• Type of contact
• Program sub groups
• Positive reponeses
• Negative responses

I have table 3  which has

• Program sub groups
• Program goups ( basically few sub groups will have the same group and for exmaple 10 sub groups will form only 2 groups)

Out of table 2 I have added a measure to show the % of positive reponses – Positive% = Positive responses/(positive reponses+negative responses)

Then I create a pivot which will have as rows  Supervisor name and as a value the Postive%

Now I want to add a second column (under values) which will rank those supervisors based on the postive% - the highest should get RANK 1 etc.

I will also want to filter the matrix to include only specific groups. 

Do you have any ideas how to achieve this?

Many thanks in advance!

[IPGeorgiev]

Any ideas anyone? 

[Greg_Deckler]

@IPGeorgiev Check out https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

Otherwise not really enough to go on. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[IPGeorgiev]

Hi @Greg_Deckler ,

I will try to simplify it, thanks for your time!

Table 1

Employee Name	Date	Type	Positive responses	Negative responses
agent1	7/22/2021	Billing	1	5
agent1	7/22/2021	Payment	2	0
agent1	7/22/2021	Charge	5	0
agent2	7/22/2021	Billing	4	1
agent2	7/22/2021	Payment	6	0
agent2	7/22/2021	Charge	2	1
agent3	7/22/2021	Billing	4	2
agent3	7/22/2021	Payment	1	0
agent3	7/22/2021	Charge	10	3
agent4	7/22/2021	Billing	4	1
agent4	7/22/2021	Payment	2	0
agent4	7/22/2021	Charge	6	0
agent5	7/22/2021	Billing	3	0
agent5	7/22/2021	Payment	5	3
agent5	7/22/2021	Charge	2	2
agent6	7/22/2021	Billing	8	1
agent6	7/22/2021	Payment	6	0
agent6	7/22/2021	Charge	7	0
agent7	7/22/2021	Billing	4	0
agent7	7/22/2021	Payment	6	1
agent7	7/22/2021	Charge	2	1
agent8	7/22/2021	Billing	4	1
agent8	7/22/2021	Payment	7	1
agent8	7/22/2021	Charge	8	2
agent9	7/22/2021	Billing	3	0
agent9	7/22/2021	Payment	5	1
agent9	7/22/2021	Charge	7	0
agent10	7/22/2021	Billing	9	0
agent10	7/22/2021	Payment	5	0
agent10	7/22/2021	Charge	4	1

Table 2

Employee Name	SupervisorName
agent1	Supervisor1
agent2	Supervisor2
agent3	Supervisor3
agent4	Supervisor1
agent5	Supervisor2
agent6	Supervisor3
agent7	Supervisor1
agent8	Supervisor2
agent9	Supervisor3
agent10	Supervisor1

There is a relationship between those two tables on EmployeeName level - Many to One

There is a measure in Table 1 :

Positive% = SUM(Table1[Positive responses]/(Table1[Postive responses]+Table1[Negative respnonses])

The final output should look like this:

Row Labels	Sum of Positive responses	Sum of Negative responses	Positive%	Rank
Supervisor1	50	9	0.847457627	2
Supervisor2	41	11	0.788461538	3
Supervisor3	51	7	0.879310345	1

Ranking here is with the following logic -the higher the Postive% the better - so the Supervisor whose team has the highest Postivie% should be first. 

Many thanks in advance!

Best regards,

Ivan