Create rank under each LOB and under their supervisor

[Ammulu_M]

I have date table & main file which has supervisor, agent id, lob for each employee,
I have aes file where I was calculating the aes attainment, (aes num/aes denom) which is a measure
I have QA file where I was calculating the QA attainment, (QA num/QA denom) which is a measure
I have absenteeism file where I was calculating the absent attainment, (absent num/absent denom) which is also a measure
now i have created another measure which is sum of all the above attainment i.e overall attainment.
I want to create rank for each employee under their lob and respective supervisor. in power bi based on the Overall attainment which varies with date.

[bhanu_gautam]

@Ammulu_M , Create a measure using DAX
Employee_Rank =
RANKX(
ALLSELECTED('Main File'[EmployeeID]),
CALCULATE([Overall_Attainment], ALLEXCEPT('Main File', 'Main File'[LOB], 'Main File'[Supervisor], 'Date'[Date])),
,
DESC,
DENSE
)

[rajendraongole1]

Hi @Ammulu_M - Can you please share some sample data to take it as reference and expected result

[Ammulu_M]

This is the main table looks like.

This are the measures I was using. I have connection to all the tables with main file throught EMP_id many to many in single direction where Main file filters the tables.
Now i want to create rank like this in the card visual 

[rajendraongole1]

Hi @Ammulu_M - as checked the attached snapshot, you can combine the individual attainment measures into an overall attainment measure as below:

Overall Attainment = [AES Attainment] + [QA Attainment] + [Absent Attainment]

create below measure to get the rank is within each LOB and supervisor.In employee Rank, you can add toverall attainment too.

Employee Rank =
VAR CurrentEmployee = SELECTEDVALUE('main file'[Employee ID])
VAR CurrentLOB = SELECTEDVALUE('main file'[LOB])
VAR CurrentSupervisor = SELECTEDVALUE('main file'[Supervisor])
RETURN
RANKX(
FILTER(
ALLSELECTED('main file'),
'main file'[LOB] = CurrentLOB &&
'main file'[Supervisor] = CurrentSupervisor
),
[Overall Attainment],
,
DESC,
DENSE
)

Hope it helps

[Ammulu_M]

Thank you rajendraongole1 for the quick response.

In the selected date range and supervisor I ahve only 34 employees but why I'm getting last rank as 73

[rajendraongole1]

Hi @Ammulu_M -There might be duplicate entries for employees which can inflate the rank count.

count the number of employees visible in the current context

EmployeeCount = COUNTROWS(
FILTER(
ALLSELECTED('EmployeeTable'),
'EmployeeTable'[Supervisor] = MAX('EmployeeTable'[Supervisor]) &&
'EmployeeTable'[LOB] = MAX('EmployeeTable'[LOB])
)
)

To rank employees by their OverallAttainment within their LOB and Supervisor

EmployeeRank = RANKX(
FILTER(
ALLSELECTED('EmployeeTable'),
'EmployeeTable'[Supervisor] = MAX('EmployeeTable'[Supervisor]) &&
'EmployeeTable'[LOB] = MAX('EmployeeTable'[LOB])
),
[OverallAttainment],
,
DESC,
DENSE
)

Try the above employe count and also Employee Rank fields in your table chart. 

still issue exist, please share sample data in excel

[Ammulu_M]

I tried the employee count i'm getting the count of all, but i want distinct