topic Re: Ranking results of complex measure across employees. in DAX Commands and Tips

Ranking results of complex measure across employees.

SACooper — Wed, 07 Sep 2022 16:06:45 GMT

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.

Re: Ranking results of complex measure across employees.

PaulOlding — Wed, 07 Sep 2022 16:08:34 GMT

Hi SACooper

You can get the rank using RANKX

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

Re: Ranking results of complex measure across employees.

SACooper — Thu, 08 Sep 2022 09:20:16 GMT

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.

Re: Ranking results of complex measure across employees.

PaulOlding — Thu, 08 Sep 2022 09:32:55 GMT

SACooper

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

Re: Ranking results of complex measure across employees.

SACooper — Thu, 08 Sep 2022 09:50:38 GMT

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?

Re: Ranking results of complex measure across employees.

PaulOlding — Thu, 08 Sep 2022 09:58:50 GMT

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/2425574#M64409

Re: Ranking results of complex measure across employees.

SACooper — Thu, 08 Sep 2022 10:19:05 GMT

OK PaulOlding Here we go .....

**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]

Re: Ranking results of complex measure across employees.

PaulOlding — Thu, 08 Sep 2022 11:00:06 GMT

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])

Re: Ranking results of complex measure across employees.

PaulOlding — Thu, 08 Sep 2022 11:00:42 GMT

Re: Ranking results of complex measure across employees.

PaulOlding — Thu, 08 Sep 2022 11:01:07 GMT

Re: Ranking results of complex measure across employees.

PaulOlding — Thu, 08 Sep 2022 11:02:00 GMT

Re: Ranking results of complex measure across employees.

SACooper — Thu, 08 Sep 2022 11:02:46 GMT

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.