How to organize and calculate standard SLA/KPI's but based on customer specific targets.

[Lorentzt]

Hello.

I have several SLA's i need to report on for our customers but with varying target. As an Example:

Number of tickets started within response time based on customers SLA

Number of tickets NOT started withing response time based on customers SLA

Tables i have today:

Ticket table (all information and values on each ticket)

Customer table (linked to ticket table trough Contact person)

My problem is twofold.

1. How can i do a count of values in Ticket Table where Responsetime is within the defined target by checking a value from another table.
2. How should i organize the tablestructure to get a good structure.

My initial idea for structure was to eighter create one collumn for each KPItarget in the Customertable or create a specific KPI target table with columns for each KPItarget and customerid.

Any suggestions and examples on how you have solved similar challenges are very welcome.

[technolog · Accepted solution]

First, let's address the table structure. Given that you have varying SLAs for different customers, it makes sense to store these SLA targets in the Customer table itself. This way, for each customer, you can have a column like 'SLA_ResponseTime' which indicates the target response time for that customer. If you anticipate having multiple KPI targets for each customer in the future, then creating a separate KPI target table linked to the Customer table via a 'CustomerID' might be a good idea. This will allow you to scale and add more KPIs without cluttering the Customer table. But if it's just one or two KPIs, adding them directly to the Customer table should suffice.

Now, for the DAX calculations:

To count the number of tickets that started within the response time based on the customer's SLA, you can use a formula like:
Tickets Within SLA =
SUMX(
RELATEDTABLE(Customer),
CALCULATE(
COUNTROWS(Ticket),
FILTER(
Ticket,
Ticket[Responsetime] <= Customer[SLA_ResponseTime]
)
)
)

This formula essentially goes through each customer, filters the tickets for that customer where the response time is within the SLA, and then counts those tickets.

For the number of tickets NOT started within the response time:
Tickets Outside SLA =
SUMX(
RELATEDTABLE(Customer),
CALCULATE(
COUNTROWS(Ticket),
FILTER(
Ticket,
Ticket[Responsetime] > Customer[SLA_ResponseTime]
)
)
)
This is similar to the previous formula but checks for tickets outside the SLA response time.

Remember to establish relationships between the tables, especially if you're using a separate KPI target table. The Ticket table should be related to the Customer table through the 'Contact person' or any other unique identifier.