SLA calculation - filter statuses->assign SLA Y/N -> calculate SLA%

[EM_Z]

Hello everyone,

I am looking for support on how to calculate SLA% when data need be filtered by status, statuses time sumed up and SLA calculated per type of activity.

My data looks like this:

Ticket	Status	Time	Type
111	unassigned	1	cooking
111	in-progress	2	cooking
111	Pending 3rd party	3	cooking
111	in-progress	1	cooking
111	draft	1	cooking
222	unassigned	1	cleaning
222	in-progress	1	cleaning
222	Pending 3rd party	2	cleaning
333	unassigned	1	cooking
333	Pending 3rd party	2	cooking
333	in-progress	1	cooking
333	Pending 3rd party	1	cooking
333	in-progress	2	cooking
444	unassigned	1	cooking
444	in-progress	1	cooking
555	unassigned	1	cleaning
555	in-progress	1	cleaning

I need to filter items in status in-progress and unassigned, sum up time for these two statuses and calculate SLA per type of activity.

Type	Ticket	Sum of Time	InSLA
cleaning	222	2	N
	555	2	Y
cooking	111	4	N
	333	4	N
	444	2	Y

which gives

Type	SLA%
cooking	33%
cleaning	50%

How do I do that?

Thanks!

EM_Z

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[AnalyticsWizard]

@EM_Z 

To calculate the SLA% based on the filtered status and summed time per type of activity using Power BI, follow these steps:

1. Import your data into Power BI. You can enter the data manually in Power BI or load it from an external source like Excel.

2. Create a calculated column to filter and sum the time for the statuses 'in-progress' and 'unassigned'. Use a DAX formula like the following:

Filtered Time =
CALCULATE(
SUM('Table'[Time]),
'Table'[Status] IN {"unassigned", "in-progress"}
)

3. Create a measure to calculate the SLA per type of activity. The SLA could be defined as the percentage of tickets that meet a certain condition. For instance, if "InSLA" means that the sum of time for 'unassigned' and 'in-progress' is less than or equal to 3, then use the following DAX formula:

SLA Measure =
VAR TotalTickets = COUNTROWS('Table')
VAR InSLATickets = CALCULATE(
COUNTROWS('Table'),
FILTER(
SUMMARIZE('Table', 'Table'[Ticket], 'Table'[Type], "SumTime", [Filtered Time]),
[SumTime] <= 3
)
)
RETURN
DIVIDE(InSLATickets, TotalTickets)

4. To see the SLA% by activity type, you might need to adjust the context of your calculation using a visual level filter or by adjusting the DAX formula to group by the type of activity. For example:

SLA% by Type =
CALCULATE(
[SLA Measure],
ALLEXCEPT('Table', 'Table'[Type])
)

5. Create a report with a table visual that groups data by 'Type' and includes the 'SLA% by Type' measure. This will give you the SLA% per activity type, as requested.

6. Additionally, to display details per ticket as in your example, create a matrix visual with 'Type' and 'Ticket' as rows, 'Sum of Time' as values, and 'InSLA' as another value calculated by a similar approach.

Here's a DAX for the 'InSLA' column:

InSLA = IF([Filtered Time] > 3, "N", "Y")

By following these steps, you should be able to visualize and calculate the SLA% per type based on the filtered status and summed times as described. Remember to adjust the logic of 'InSLA' and SLA thresholds as per your specific requirements.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[EM_Z]

Thank you @AnalyticsWizard .

Following your instructions I faced below issue. PBI does allow me to pick time calculated in step 2. What could be the reason?

[EM_Z]

Hi Jing,

Thank you for your answer.

<=2h in SLA, >2h not in SLA. 
*with that SLA for cleaning would be 100%, apologies for this mistake

I created simlar formula, which seems to work.

if((CALCULATE(SUM('XXX (2)'[Business Hours]), KEEPFILTERS('XXX'[Status] in {"In Process", "Unassigned"}))<=2), "Yes","No"))

but now coming back to my biggest issue right now, how to calculate SLA %? SLA needs to be calculated per ticket and the per day/month etc. -> SLA% is count of items for sum processing time lower than 2h and devided by all tickets raised during a day. 

I hopped that I will be able to refer to the 1st measure when creating 2nd measure (count Yes on tickets with status unassigned and devide by items with Yes and No with status unassiged) but it does not work - I assue the reason is returning Yes and No in the 1st measure? If my assumption is right, then how to move to SLA% calculation using your solution? 

At the end graph would need to show daily SLA. 

Thank you for your further help.

BR,

EM_Z

[v-jingzhan-msft]

Hi @EM_Z 

To calculate the sum of time by filtering the status, you can create the following measure.

Sum of Time = CALCULATE(SUM('Table'[Time]),'Table'[Status] IN {"in-progress", "unassigned"})

For the SLA calculation, can you explain how to decide whether it is Y or N? And to calculate the SLA%, should it divide the sum of "Y" by the total of each type?

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!