Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Hi,
On the Data that you have shared, show the expected result very clearly.
Hi there
Here's how I would tackle it as a starting point (PBIX attached):
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]
)
Regards,
Owen
@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.
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
@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
@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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
72 | |
68 | |
45 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |