Forum Discussion

kkane's avatar
kkane
Regular Visitor
2 years ago
Solved

DAX KPI % Measure

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:

 

ServiceTeamNHSNumberReferralDateAttendElapsed Working DaysElapsed Working WeeksElapsed DaysElapsedWeeksNumber
a1119/01/202312/10/202319138.2266381
b2231/01/202309/10/202318036011
c3328/03/202302/10/202313527188271
d4403/05/202325/10/202312625.2175251
e5524/04/202309/10/202312124.2168241
f1622/05/202318/10/202310821.6149221
g2708/06/202327/10/202310220.4141211
h3825/05/202310/10/20239919.8138201
i4905/06/202313/10/20239519130191
j51009/06/202317/10/20239318.6130191
k11120/06/202320/10/20238917.8122181
l21220/06/202320/10/20238917.8122181
m31320/06/202320/10/20238917.8122181
n41420/06/202320/10/20238917.8122181
o51509/06/202310/10/20238817.6123181
p11620/06/202318/10/20238717.4120181
q21709/06/202309/10/20238717.4122181

 

 

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's avatar
    Anonymous
    2 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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