Percentage of items assigned to top X staff members

[redhughes]

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

[Ashish_Mathur]

Hi,

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

[OwenAuger]

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

[redhughes]

@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.

[v-deddai1-msft]

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

[OwenAuger · Accepted solution]

@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 =
            ADDCOLUMNS (
                VALUES ( Appointments[Staff] ),
                "@RankAdjustment",
                    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

[redhughes]

@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. 

[OwenAuger]

@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