% of cases met within specific KPI (multiple used)

[Anonymous]

Hi, this is my first post on the community so apologies if I cause confusion. I've seen that its possible to calculate the % of cases/times in which something meets a KPI, vs doesn't (e.g. hit a KPI 50% of the time). Which presumably then could be shown in a line graph by month?

My case is complicated by the fact I am working with multiple KPIs (8 in total - split 4 each across another field). My data is a split of Incidents and Requests, and there are 4 KPI targets for each, which are around the amount of time it takes to resolve a case (ie Resolved Date - Opened Date). I'm trying to work out if there is a way the system can identify which KPI its supposed to be calculating based on. 

For example I have a KPI that all low priority incidents need to be resolved by 5 days, but all low priority requests need to be resolved by 20 days ie a different number - similar for medium, high and extremely high priority incidents & requests. Is this doable, or is it something I need to continue doing on Excel?

My overall aim is to get a line graph that would enable me to show the worst performing areas by month (ie those that are hitting a filtered KPI the least. 

Thanks! I can provide an exerpt of the data if that helps? I'm stumped! 

[BA_Pete · Accepted solution]

Hi @Anonymous ,

I might get shot down here but, in my opinion, if you can do it in Excel, you can do it better in Power BI.

You will need to provide an excerpt of your data I think as it's hard to visualise without it.

Make sure you remove any sesitive data please, and make sure to include all the columns that are there. This is how we will identify which KPI we are calculating, assuming that there are columns that state whether the entry is a Request or an Incident etc.

Pete

[Anonymous]

Hello! 

Below I have put 2 tables, the first being the core fields that should enable the reporting to be built, and the 2nd being the relevant KPIs to that data. For context, the fields I would want to be able to filter the data by (in the 1st table below) are 'Priority', 'Task Type' and 'Team'. The dates opened and closed are date/time because some of the KPIs are less than 1 day, as in the 2nd table. 

ID	Priority	Task Type	Team	Opened	Closed
INC0335643	4 - Low	Incident	Service Desk GB	02/01/2020 08:10	02/01/2020 08:23
INC0335644	4 - Low	Incident	SD Tier 2	02/01/2020 08:11	02/01/2020 08:25
INC0335638	2 - High	Incident	Infrastructure Team	02/01/2020 07:31	02/01/2020 08:25
RITM0481604	4 - Low	Requested Item	SD Tier 4 Specialists	01/01/2020 01:00	02/01/2020 08:55
RITM0481754	4 - Low	Requested Item	Service Desk GB	02/01/2020 08:52	02/01/2020 09:01
INC0335648	4 - Low	Incident	SD Tier 2	02/01/2020 08:54	02/01/2020 09:21
INC0335651	4 - Low	Incident	SD Tier 2	02/01/2020 09:21	02/01/2020 09:54
INC0335658	4 - Low	Incident	Service Desk GB	02/01/2020 09:49	02/01/2020 09:56
INC0335657	2 - High	Incident	Applications	02/01/2020 09:46	02/01/2020 09:57
RITM0481795	4 - Low	Requested Item	Service Desk GB	02/01/2020 10:23	02/01/2020 10:24
INC0335656	1 - Critical	Incident	Infrastructure Team	02/01/2020 09:45	02/01/2020 10:35
RITM0481796	1 - Critical	Requested Item	Service Desk GB	02/01/2020 10:27	02/01/2020 11:09
INC0335668	4 - Low	Incident	SD Tier 2	02/01/2020 11:04	02/01/2020 11:32
INC0335670	4 - Low	Incident	SD Tier 2	02/01/2020 11:32	02/01/2020 11:39
RITM0481600	2 - High	Requested Item	SD Tier 2	01/01/2020 00:00	02/01/2020 11:54
RITM0481817	4 - Low	Requested Item	SD Tier 2	02/01/2020 12:11	02/01/2020 12:37
RITM0481819	4 - Low	Requested Item	Infrastructure Team	02/01/2020 12:33	02/01/2020 12:46
INC0335679	4 - Low	Incident	Applications	02/01/2020 12:22	02/01/2020 13:09
RITM0481828	4 - Low	Requested Item	SD Tier 2	02/01/2020 13:31	02/01/2020 13:33
RITM0481829	4 - Low	Requested Item	SD Tier 2	02/01/2020 13:35	02/01/2020 13:39
INC0335680	4 - Low	Incident	SD Tier 2	02/01/2020 12:26	02/01/2020 13:51
RITM0481832	4 - Low	Requested Item	SD Tier 2	02/01/2020 13:42	02/01/2020 14:14
RITM0481809	4 - Low	Requested Item	SD Tier 2	02/01/2020 11:19	02/01/2020 14:15
INC0335681	4 - Low	Incident	Service Desk GB	02/01/2020 12:32	02/01/2020 15:25
RITM0481857	4 - Low	Requested Item	SD Tier 2	02/01/2020 15:40	02/01/2020 15:44
INC0335672	4 - Low	Incident	SD Tier 2	02/01/2020 11:36	02/01/2020 15:46
INC0335700	4 - Low	Incident	SD Tier 2	02/01/2020 16:12	02/01/2020 16:22
INC0335684	4 - Low	Incident	SD Tier 2	02/01/2020 13:10	02/01/2020 16:34
INC0335696	4 - Low	Incident	Service Desk GB	02/01/2020 15:03	02/01/2020 16:40
RITM0481836	4 - Low	Requested Item	Global Service Desk	02/01/2020 14:14	02/01/2020 16:44

KPIs below:

Task Type	Priority	KPI
Incident	4 - Low	5 days
Incident	2 - High	8 hours
Request	4 - Low	20 days
Request	1 - Critical	2 days

Hope that clarifies things - my aim is to be able to build a filterable line graph showing the 'Team' that is meeting the KPI the least frequently (ie lowest %) across the reported period. 

[BA_Pete]

Hi @Anonymous ,

A couple of points:

- I had to edit your example data as there were no KPI failures in the original data

- Some of the [Task Type]/[Priority] pairs had no KPI criteria, so I've just categorised those as 'KPI Met = No'

First of all, the data. I added a [kpiMet] column in Power Query to assess each row based on your criteria. Here's the code you can paste into a blank query so you can see the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZZPb9QwEMW/irXnonr8P77RViqRgEO7t6qHkJoSke4uSRa+PnZgIWM3WRxuq6fqN5M3b8Z9eNiUH68p51IJvrnYCPKGvN//8L/KXd08ud3gf9677ntTO3Lj+q/k9sorlF1SuGSUUUKNBRokPZUY3zxeTNhiln1Dto3rCEupkEhMIio3/i+Yp75rnr9gbLn73FX90B3r4dg5snXVS0TTls8WuCu3H6gwoCju+859O7p+cE+kHNzLpHtB7g+ubqq26Yc+UOEvFSwN/gCfFpK4kJbnCp0ZgYz9KywFPAKTP4KxK0xlmCohlzoivB1I8oUQdb7XRR8KK4pEkgqz9Wxo3h4ObVNXQ7Pf9SlZBUkgssZDLOR/DBFoWJpEiowJTYCvcN01g2+1zU29/w6ZFOFRGItXquR/jMZ+gd+DAn2Myk1kQIhE4gxRNc2n8lekIroEFMdm9hSwaP3pr/XH9N95P9G95/92Z+I+WXIlvcR1BC+W4WdTE5ghmkxOJYH3Shdzvs+vlaew+JP4KSZ/+mdmnTk8ufBB4hH8jDkL8HiR+Ck0J0tMdhS9HyqhSsAtj2ld1bIIEkx2UliIkkhX+gEWotMb4Pi9NvOPxeIxYemCyvipNnLlCkkr4v30Ej67muUO0h+QcZAGUdHGaJodD2UhlRi+fyb3Xy2fC4gdUJZjB8YnYcXopKXxo6aC4TjQanl0t+3+U9WSaaU0avHDoMYhPv4E", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Priority = _t, #"Task Type" = _t, Team = _t, Opened = _t, Closed = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Priority", type text}, {"Task Type", type text}, {"Team", type text}, {"Opened", type datetime}, {"Closed", type datetime}}),
    addDtOpened = Table.AddColumn(chgAllTypes, "dtOpened", each DateTime.Date([Opened]), type date),
    addMthOpened = Table.AddColumn(addDtOpened, "mthOpened", each Text.Combine({Text.Start(Date.MonthName([dtOpened]), 3), Text.Start(Text.From(Date.Year([dtOpened])), 2)}, " "), type text),
    addKpiMet = Table.AddColumn(addMthOpened, "kpiMet", each if [Task Type] = "Incident" and [Priority] = "4 - Low" and Number.From(Duration.From([Closed] - [Opened])) < 5 then "Yes" else
if [Task Type] = "Incident" and [Priority] = "2 - High" and Number.From(Duration.From([Closed] - [Opened])) < 0.333 then "Yes" else
if [Task Type] = "Requested Item" and [Priority] = "4 - Low" and Number.From(Duration.From([Closed] - [Opened])) < 20 then "Yes" else
if [Task Type] = "Requested Item" and [Priority] = "1 - Critical" and Number.From(Duration.From([Closed] - [Opened])) < 2 then "Yes" else "No", type text)
in
    addKpiMet

Note that I've added [dtOpened] and [mthOpened] fields to assist with reporting axes. These shouldn't be necessary if you're using a calendar table as is best practice.

The percentage KPI met measure is as follows:

_kpiMet% = 
VAR noofReports =
DISTINCTCOUNT(aTable[ID])
VAR kpiMet =
CALCULATE(
    DISTINCTCOUNT(aTable[ID]),
    aTable[kpiMet] = "Yes"
) + 0
RETURN
DIVIDE(kpiMet, noofReports, 0)

This gives me the following ouput in line with the limitations noted above:

Pete

[BA_Pete]

Hi @Anonymous ,

Thanks for accepting my answer as the solution - I'm glad it did what you needed it to.

Could I just ask that you accept the answer with the actual code/measures in as the solution please? This will help others' looking for help in this area to find the actual solution quicker.

Thanks,

Pete