KPI calculation for a dynamic range of activities

[salafoo]

Hello All,

I hope everyone is doing well.

I am currently working on developing a code to measuring KPI of an employee/department using a dataset that may contain a dynamci range of similar activities per user.

Below an example of activities conducted for a payment request, where I have added column 'Duration' manually using excel.

Parent Ref.	Status Report	Created On	Duration (end date - start date)
Payment Request 01	Customer: Submit Payment Request	21-03-19	0
Payment Request 01	Verifier: Send Back to Portal	08-04-19	18
Payment Request 01	Customer: Submit Payment Request	19-05-19	41
Payment Request 01	Verifier: Submit for Checker Review	20-05-19	1
Payment Request 01	Checker: Send Back to Maker	21-05-19	1
Payment Request 01	Verifier: Submit for Checker Review	25-05-19	4
Payment Request 01	Checker: Send Back to Maker	26-05-19	1
Payment Request 01	Verifier: Send Back to Portal	10-06-19	15
Payment Request 01	Customer: Submit Payment Request	20-06-19	10
Payment Request 01	Verifier: Submit for Checker Review	25-06-19	5
Payment Request 01	Checker: Submit to Auditor	28-06-19	3
Payment Request 01	Accountant Mark as Committed - Transfer	29-06-19	1

On the other hand, I have a table summurizies the main informantion of the payment request (e.g. amount, customer name, etc). where I would like to add to it the duration taken by each user using the above mentioned table.

The total  duration should be as following:

Verifier Duration	43
Checker Duration	5
Accountatn Duration	1

Would you mind helping me with finding the right code for this report.

many thanks

[Samarth_18 · Accepted solution]

Hi @salafoo ,

If I took your question correctly then you can create a new table with below code:-

Table 2 =
SUMMARIZE (
    'Table (3)',
    "Verifier Duration",
        CALCULATE (
            SUM ( 'Table (3)'[Duration (end date - start date)] ),
            CONTAINSSTRING ( 'Table (3)'[Status Report], "Verifier" )
        ),
    "Checker Duration",
        CALCULATE (
            SUM ( 'Table (3)'[Duration (end date - start date)] ),
            CONTAINSSTRING ( 'Table (3)'[Status Report], "Checker" )
        ),
    "Accountant Duration",
        CALCULATE (
            SUM ( 'Table (3)'[Duration (end date - start date)] ),
            CONTAINSSTRING ( 'Table (3)'[Status Report], "Accountant" )
        )
)

Output:-

Thanks,

Samarth