Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
SACooper
Helper II
Helper II

Ranking results of complex measure across employees.

Good Afternoon Experts,

 

I am looking to end up showing the results of a measure to an employee in the form of something like you are in the top 10/10/30/40 etc. % of employees (in terms of performance against target)

 

Performance agaisnt target is returned by:

 

Performance

VAR _flexitime = SUM('flexi UD_FullReport'[Day_TotalTime])
VAR _decimalHours = _flexitime / 60
VAR _dayValue = TIME(7,15,0) * 24
VAR _targetPerHour = _dayValue / [Target_IPD]
VAR _Target = ROUNDDOWN(_decimalHours / _targetPerHour,0)
 
RETURN

DIVIDE(COUNT(CompletedItems[WorkType]) - _Target, _Target)
 
I've been fiddling around all day and to be frank have got absolutely nowhere in terms of being able to then return the performance of the selected employee as a rank or even a decentile (think this is the correct word)  of all performances.
1 ACCEPTED SOLUTION

@SACooper

Here's the results I get with the sample data, these measures and this model

 

 Performance =

VAR _flexitime = SUM('flexi UD_FullReport'[Day_TotalTime])

VAR _decimalHours = _flexitime / 60 VAR _dayValue = TIME(7,15,0) * 24

VAR _targetPerHour = _dayValue / 43 // [Target_IPD]

VAR _Target = ROUNDDOWN(_decimalHours / _targetPerHour,0)

RETURN DIVIDE(COUNT('Completed Items'[WorkType]) - _Target, _Target) 

 

Employee Rank = RANKX(ALL(ActiveDirectory[displayName]), [Performance])
 
PaulOlding_1-1662634600880.png

 

View solution in original post

11 REPLIES 11
PaulOlding
Solution Sage
Solution Sage

Hi @SACooper 

You can get the rank using RANKX

Employee Rank = RANKX(ALL(Table[employee_id_column]), [Performance])

 

Hi @PaulOlding ,

 

Thank you for this, unfortuantely doesn't seem to work, its scoring everyone has 68 (the number of employees), I would have expected as I change the employee slicer that, this value would change. I have checked by placing another card with the performance to target value and this is changing to reflect the employee's performance correctly.

@SACooper 

Can you provide some example data in table form (ie not a screenshot)?

Of course @PaulOlding, what would be most helpful a table of the performance results that the measure would calculate? or the tables that, that particular measure draws from?

@SACooper the source data that the measure draws from.

 

see this post for more:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p... 

OK @PaulOlding Here we go .....

 

ActiveDirectory
displayName | person.cn | user.mail | user.userPrincipalName | user.manager.displayName | user.manager.userPrincipalName

Scott Cooper | SCooper | scott.cooper@emailaddress.com | Joe Bloggs | joe.blogs@emailaddress.com
Jane Doe | JADoe | jane.doe@emailaddress.com | Joe Bloggs | joe.blogs@emailaddress.com
John Doe | JBDoe | john.doe@eamiladdress.com | Joe Bloggs | joe.blogs@emailaddress.com
...

Flexi UD_fullReport
prof_adlogin | ProfFullNameCached | Day_FlexiDayDate | Day_TotalTime
Scooper | Scott Cooper | 01/06/2022 | 435
JADoe | Jane Doe | 01/06/2022 | 420
JBDoe | John Doe | 01/06/2022 | 500
Scooper | Scott Cooper | 02/06/2022 | 450
JADoe | Jane Doe | 02/06/2022 | 475
JBDoe | John Doe | 02/06/2022 | 300
...

Completed Items
WorkType | ProcessedDate | ProcessedBy | Time
EMAILOUT | 01/06/2022 | SCooper | 09:11:31
EMAILIN | 01/06/2022 | JADoe | 08:45:16
ATLCHECK | 01/06/2022 | JBDoe | 09:21:16
SRDRED | 01/06/2022 | SCooper | 11:11:58
SRDRED | 02/06/2022 | JBdoe | 10:19:16
....

 

**currently target is a flat 43 items per day (7 hours 15 minutes) but this will be developed to be more dynamic taking into account departmental actvitity and would be stored for the user in the measure [Target_IPD]

@SACooper

Here's the results I get with the sample data, these measures and this model

 

 Performance =

VAR _flexitime = SUM('flexi UD_FullReport'[Day_TotalTime])

VAR _decimalHours = _flexitime / 60 VAR _dayValue = TIME(7,15,0) * 24

VAR _targetPerHour = _dayValue / 43 // [Target_IPD]

VAR _Target = ROUNDDOWN(_decimalHours / _targetPerHour,0)

RETURN DIVIDE(COUNT('Completed Items'[WorkType]) - _Target, _Target) 

 

Employee Rank = RANKX(ALL(ActiveDirectory[displayName]), [Performance])
 
PaulOlding_1-1662634600880.png

 

PaulOlding_0-1662634831908.png

 

@PaulOlding Thank you it obviously works there must be something that I thought was unrelated, actually having an unintended influence ... i'll have a dig through ... chances are its in my data model somewhere.

 

Thank you for your time - really appreciate it.

PaulOlding_1-1662634855570.png

 

PaulOlding_2-1662634909936.png

 

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.