Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |