DAX KPI % Measure

[kkane]

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:

% KPI 1A = (CALCULATE(SUM('vw_PBI_WaitingTimes'[Elapsed Working Days]),('vw_PBI_WaitingTimes'[Elapsed Working Days] <= 20))/sum('vw_PBI_WaitingTimes'[Number]))

And also, more points if you can help me with a WHERE Type clause, WHERE Urgency = "Routine" AND Service KPI Group = "A"

[Anonymous · Accepted 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