cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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:

 

StaffClientDateTime
Staff1Joan Smith01/09/202010:00
Staff2Joan Smith01/09/202019:00
Staff1Joan Smith02/09/202010:00
Staff3Joan Smith02/09/202019:00
Staff1Joan Smith03/09/202010:00
Staff4Joan Smith03/09/202019:00
Staff1Joan Smith04/09/202010:00
Staff5Joan Smith04/09/202019:00
Staff1Joan Smith05/09/202010:00
Staff6Joan Smith05/09/202019:00
Staff1Joan Smith06/09/202010:00
Staff2Joan Smith06/09/202019:00
Staff1Joan Smith07/09/202010:00
Staff2Joan Smith07/09/202019:00
Staff1Joan Smith08/09/202010:00
Staff3Joan Smith08/09/202019:00
Staff1Joan Smith09/09/202010:00
Staff4Joan Smith09/09/202019:00
Staff1Joan Smith10/09/202010:00
Staff5Joan Smith10/09/202019:00
Staff1John Smith01/09/202008:00
Staff1John Smith01/09/202017:00
Staff3John Smith02/09/202008:00
Staff1John Smith02/09/202017:00
Staff4John Smith03/09/202008:00
Staff1John Smith03/09/202017:00
Staff5John Smith04/09/202008:00
Staff1John Smith04/09/202017:00
Staff6John Smith05/09/202008:00
Staff2John Smith05/09/202017:00
Staff7John Smith06/09/202008:00
Staff2John Smith06/09/202017:00
Staff8John Smith07/09/202008:00
Staff2John Smith07/09/202017:00
Staff9John Smith08/09/202008:00
Staff1John Smith08/09/202017:00
Staff10John Smith09/09/202008:00
Staff1John Smith09/09/202017:00
Staff11John Smith10/09/202008:00
Staff2John Smith10/09/202017:00
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

 

OwenAuger_0-1600496306002.png

 

 

Regards,

Owen

 


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

7 REPLIES 7
Highlighted
Super User I
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

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted

@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%
150%
265%
375%
485%
595%
6100%

 

rather than 95% from 3 Top N upwards.

Highlighted

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

 

OwenAuger_0-1600496306002.png

 

 

Regards,

Owen

 


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Super User III
Super User III

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

Capture2.PNG

 

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

Highlighted

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

Highlighted

@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

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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