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 all,
I have a wait times dataset and am trying to create a measure that will show the % of compliance to the wait time target.
Out of all the patients who had a first appointment (clock stop) in X Month, how many were below the wait time target.
So for example, if the target was <=20 days. Here is an small sample dataset anonymised:
Service | Team | NHSNumber | ReferralDate | Attend | Elapsed Working Days | Elapsed Working Weeks | Elapsed Days | ElapsedWeeks | Number |
a | 1 | 1 | 19/01/2023 | 12/10/2023 | 191 | 38.2 | 266 | 38 | 1 |
b | 2 | 2 | 31/01/2023 | 09/10/2023 | 180 | 36 | 0 | 1 | 1 |
c | 3 | 3 | 28/03/2023 | 02/10/2023 | 135 | 27 | 188 | 27 | 1 |
d | 4 | 4 | 03/05/2023 | 25/10/2023 | 126 | 25.2 | 175 | 25 | 1 |
e | 5 | 5 | 24/04/2023 | 09/10/2023 | 121 | 24.2 | 168 | 24 | 1 |
f | 1 | 6 | 22/05/2023 | 18/10/2023 | 108 | 21.6 | 149 | 22 | 1 |
g | 2 | 7 | 08/06/2023 | 27/10/2023 | 102 | 20.4 | 141 | 21 | 1 |
h | 3 | 8 | 25/05/2023 | 10/10/2023 | 99 | 19.8 | 138 | 20 | 1 |
i | 4 | 9 | 05/06/2023 | 13/10/2023 | 95 | 19 | 130 | 19 | 1 |
j | 5 | 10 | 09/06/2023 | 17/10/2023 | 93 | 18.6 | 130 | 19 | 1 |
k | 1 | 11 | 20/06/2023 | 20/10/2023 | 89 | 17.8 | 122 | 18 | 1 |
l | 2 | 12 | 20/06/2023 | 20/10/2023 | 89 | 17.8 | 122 | 18 | 1 |
m | 3 | 13 | 20/06/2023 | 20/10/2023 | 89 | 17.8 | 122 | 18 | 1 |
n | 4 | 14 | 20/06/2023 | 20/10/2023 | 89 | 17.8 | 122 | 18 | 1 |
o | 5 | 15 | 09/06/2023 | 10/10/2023 | 88 | 17.6 | 123 | 18 | 1 |
p | 1 | 16 | 20/06/2023 | 18/10/2023 | 87 | 17.4 | 120 | 18 | 1 |
q | 2 | 17 | 09/06/2023 | 09/10/2023 | 87 | 17.4 | 122 | 18 | 1 |
I thought I could do the following, but it's not working:
And also, more points if you can help me with a WHERE Type clause, WHERE Urgency = "Routine" AND Service KPI Group = "A"
Solved! Go to Solution.
HI @kkane,
I think the calculation logic is calculate the condition row count with all rows to get percentage instead of total elapsed days divide with row count. For this scenario, you can try to use the following measure formula if helps:
% KPI 1A =
CALCULATE (
DIVIDE (
CALCULATE (
SUM ( 'vw_PBI_WaitingTimes'[Number] ),
'vw_PBI_WaitingTimes'[Elapsed Working Days] <= 20
),
SUM ( 'vw_PBI_WaitingTimes'[Number] ),
-1
),
ALLSELECTED ( 'vw_PBI_WaitingTimes' )
)
Regards,
Xiaoxin Sheng
HI @kkane,
I think the calculation logic is calculate the condition row count with all rows to get percentage instead of total elapsed days divide with row count. For this scenario, you can try to use the following measure formula if helps:
% KPI 1A =
CALCULATE (
DIVIDE (
CALCULATE (
SUM ( 'vw_PBI_WaitingTimes'[Number] ),
'vw_PBI_WaitingTimes'[Elapsed Working Days] <= 20
),
SUM ( 'vw_PBI_WaitingTimes'[Number] ),
-1
),
ALLSELECTED ( 'vw_PBI_WaitingTimes' )
)
Regards,
Xiaoxin Sheng
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 |
---|---|
44 | |
28 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |