cancel
Showing results for
Did you mean:
Helper II

## Percentage of items assigned to top X staff members

Hi,

We're trying to figure out the best way to show how many different staff members were assigned to a particular client over a period of time, and what % of those assignments were done by top X staff members (in terms of count).

In practice we'd like to be able to say "Joan Smith had 20 appointments, and 75% of them were assigned to 3 staff members", and ultimately "90% of our clients had at least 75% of their appointments assigned to 3 staff members".

This will help us make sure clients are seeing the same faces, and build good relationships while staff members gain 'specialist knowledge'.

Can I please get some advice on how to create a measure that would calculate it for the top 3 or 4 staff members (i.e. the values 3 and 4 being fixed), and if it's possible to make this value a variable that you can select from a list or enter into the report. Thank you again for all your input!!

Data looks a bit like this:

 Staff Client Date Time Staff1 Joan Smith 01/09/2020 10:00 Staff2 Joan Smith 01/09/2020 19:00 Staff1 Joan Smith 02/09/2020 10:00 Staff3 Joan Smith 02/09/2020 19:00 Staff1 Joan Smith 03/09/2020 10:00 Staff4 Joan Smith 03/09/2020 19:00 Staff1 Joan Smith 04/09/2020 10:00 Staff5 Joan Smith 04/09/2020 19:00 Staff1 Joan Smith 05/09/2020 10:00 Staff6 Joan Smith 05/09/2020 19:00 Staff1 Joan Smith 06/09/2020 10:00 Staff2 Joan Smith 06/09/2020 19:00 Staff1 Joan Smith 07/09/2020 10:00 Staff2 Joan Smith 07/09/2020 19:00 Staff1 Joan Smith 08/09/2020 10:00 Staff3 Joan Smith 08/09/2020 19:00 Staff1 Joan Smith 09/09/2020 10:00 Staff4 Joan Smith 09/09/2020 19:00 Staff1 Joan Smith 10/09/2020 10:00 Staff5 Joan Smith 10/09/2020 19:00 Staff1 John Smith 01/09/2020 08:00 Staff1 John Smith 01/09/2020 17:00 Staff3 John Smith 02/09/2020 08:00 Staff1 John Smith 02/09/2020 17:00 Staff4 John Smith 03/09/2020 08:00 Staff1 John Smith 03/09/2020 17:00 Staff5 John Smith 04/09/2020 08:00 Staff1 John Smith 04/09/2020 17:00 Staff6 John Smith 05/09/2020 08:00 Staff2 John Smith 05/09/2020 17:00 Staff7 John Smith 06/09/2020 08:00 Staff2 John Smith 06/09/2020 17:00 Staff8 John Smith 07/09/2020 08:00 Staff2 John Smith 07/09/2020 17:00 Staff9 John Smith 08/09/2020 08:00 Staff1 John Smith 08/09/2020 17:00 Staff10 John Smith 09/09/2020 08:00 Staff1 John Smith 09/09/2020 17:00 Staff11 John Smith 10/09/2020 08:00 Staff2 John Smith 10/09/2020 17:00
1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

@redhughes, sure thing 🙂

Rather than using TOPN, we can use RANKX instead, and adjust the rank based on [Appointment Count] to include a tie-breaker based on the Staff name.

Appointment Count Top N Staff =
VAR N = [Top Staff Value]
VAR StaffCount = [Staff Count]
RETURN
IF (
N <= StaffCount,
// For each Staff member
// calculate @RankAdjustment which is the rank of the Staff name
// divided by StaffCount + 1.
// This is used to break ties when ranking Appointment Count.
VAR Staff =
VALUES ( Appointments[Staff] ),
DIVIDE (
RANKX ( VALUES ( Appointments[Staff] ), Appointments[Staff] ),
StaffCount + 1
)
)
VAR TopStaff =
FILTER (
Staff,
RANKX ( Staff, RANKX ( Staff, [Appointment Count],, ASC ) + [@RankAdjustment] ) <= N
)
RETURN
CALCULATE ( [Appointment Count], TopStaff )
)

PBIX attached with the adjusted measure.

Regards,

Owen

Owen Auger

7 REPLIES 7
Super User I

Hi there

Here's how I would tackle it as a starting point (PBIX attached):

• Create a What If Parameter to handle the "number of staff" parameter. I created it in a table called Top Staff with the range from zero to total number of staff in the Appointments table.
• Create measures as follows

Top Staff = GENERATESERIES(0, DISTINCTCOUNT ( Appointments[Staff] ), 1)​

Staff Count =
DISTINCTCOUNT ( Appointments[Staff] )

Appointment Count =
COUNTROWS ( Appointments )

Appointment Count Top N Staff =
VAR N =
[Top Staff Value]
VAR StaffCount =
[Staff Count]
RETURN
IF (
N <= StaffCount,
VAR TopStaff =
TOPN (
N,
VALUES ( Appointments[Staff] ),
[Appointment Count]
)
RETURN
CALCULATE (
[Appointment Count],
TopStaff
)
)

Appointment % Top N Staff =
DIVIDE (
[Appointment Count Top N Staff],
[Appointment Count]
)​

• Then you can either set the Top Staff parameter to a particular value and display [Appointment % Top N Staff]. Or plot [Appointment % Top N Staff] against Top Staff.
• Note that in the case of ties for the Nth most common staff member, all the tied values are counted as rank N. You could change this behaviour by tweaking the arguments to TOPN.
• To produce outputs like "90% of our clients had at least 75% of their appointments assigned to 3 staff members" would require a further measure based on the above..

Regards,

Owen

Owen Auger

Helper II

@OwenAuger  That's great, thank you. How would I have to tweak the TOPN arguments to change the tied values behaviour? In this case, we'd prefer Joan Smith's % scores to be:

 Top N % 1 50% 2 65% 3 75% 4 85% 5 95% 6 100%

rather than 95% from 3 Top N upwards.

Super User I

@redhughes, sure thing 🙂

Rather than using TOPN, we can use RANKX instead, and adjust the rank based on [Appointment Count] to include a tie-breaker based on the Staff name.

Appointment Count Top N Staff =
VAR N = [Top Staff Value]
VAR StaffCount = [Staff Count]
RETURN
IF (
N <= StaffCount,
// For each Staff member
// calculate @RankAdjustment which is the rank of the Staff name
// divided by StaffCount + 1.
// This is used to break ties when ranking Appointment Count.
VAR Staff =
VALUES ( Appointments[Staff] ),
DIVIDE (
RANKX ( VALUES ( Appointments[Staff] ), Appointments[Staff] ),
StaffCount + 1
)
)
VAR TopStaff =
FILTER (
Staff,
RANKX ( Staff, RANKX ( Staff, [Appointment Count],, ASC ) + [@RankAdjustment] ) <= N
)
RETURN
CALCULATE ( [Appointment Count], TopStaff )
)

PBIX attached with the adjusted measure.

Regards,

Owen

Owen Auger

Super User III

Hi,

On the Data that you have shared, show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Microsoft

Hi @redhughes ,

OwenAuger  has provided a good solution , and you can also refer to the following measure:

appointments by topn staff =
VAR N =
MAX ( 'topn'[Value] )
RETURN
SUMX (
TOPN (
N,
SUMMARIZE (
'Table',
'Table'[Staff],
"appointments by staff", COUNTROWS ( 'Table' ),
"break ties", SUMX ( 'Table', VALUE ( 'Table'[Date] ) )
),
[appointments by staff] + [break ties]
),
[appointments by staff]
)

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQQoTZUt7KlHoj7Cl3...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Helper II

@OwenAuger - thanks again for the quick response! just a quick question, what's the significance of the IF statement (i.e. N <= StaffCount)? let's say a client has 7 appointments and they're all with only 2 of our staff members, then it would make sense their 'continuity measure' should be 100% at Top Staff = 3 but because of that IF statement, their measure is blank.

Super User I

@redhughes good point - that was primarily for the chart as I thought it looked better when the cumulative % ended at the total number of staff.

However, in general I can see that you wouldn't want to blank it out, so you can remove the IF if you like.

You could have two versions of the measure with & without the IF if needed.

Regards,

Owen

Owen Auger

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors