Calculated KPI based on two other columns

[mihaita_baro]

Hi guys,

I have a small issue with my KPI6 calculation formula, i don't seems to find a calculation for my KPI6

Clients must have at least 2 reviews meeting the following criteria:
- Session Type is one of the following: Treatment; Assessment and Treatment; Review and Treatment;
And
- Attendance Status is one of the following: On time/ahead of staff; Late, was seen; or Blank 

And 

Clients must score revgad7anxiety_Result_raw>7 or revphq9depression_Result_raw>9 at Initial Review and
score revgad7anxiety_Result_raw<8 and revphq9depression_Result_raw<10 at Final Review (last review being the latest one with PHQ and GAD assessment); 

I have created another  two columns to see if initial review and last review qualify for KPI6 based on revgad7anxiety_Result_raw and revphq9depression_Result_raw and the sessions .

All i need now is to calculate KPI6 based on these two columns but i don't seems to find a way.

The output is the column "KPI6"

 

 

[Anonymous · Accepted solution]

Hi @mihaita_baro ,

Please try below steps:

1. below is my test table

Table:

2. add a new column with below dax formula

Column =
VAR cur_clientID = 'Table'[Client ID]
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Client ID] = cur_clientID )
VAR _a =
    SUMX ( tmp, [KPI6 Initial Review] + [KPI6 Last Review] )
RETURN
    IF (
        _a = 2
            && 'Table'[KPI6 Initial Review] = BLANK ()
            && 'Table'[KPI6 Last Review] = 1,
        1,
        BLANK ()
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[claymcooper]

Try this: Calculate(Count(clientid),KPI6=1)

[mihaita_baro]

Hi @claymcooper 

That column is the  desired output i wanna calculate.

It can be either a calculated column or a measure that count how many clients have KPI6 Initial Review =1 and KPI6 Last review =1

This is what i came up with but doesn't calculate properly

KPI6 FTB =

CALCULATE(DISTINCTCOUNT(Appointment_FTB[clientId]),

KEEPFILTERS(

FILTER(ALL(Appointment_FTB[KPI6 Initial Review],Appointment_FTB[KPI6 Last Review]),

Appointment_FTB[KPI6 Initial Review]=1 || Appointment_FTB[KPI6 Last Review]=1

)

)

)

[claymcooper]

Can you enlarge the image or provide a sample pbix? I can't read the screenshot provided.

[mihaita_baro]

Hi @claymcooper 

I've added it again to original post

[claymcooper]

Thanks. So from your example are you trying to evaluate how many clients meet the requirements for KPI6? In your screenshot the total would equal 4. Correct?

[mihaita_baro]

Hi @claymcooper 

Yes that's correct. 4 clients eligible for KPI6. I have tried some thing but i am missing something