Using percentiles or other aggregated summary statistics to decide on thresholds for SLAs

[gemcityzach]

Hey folks, I need to establish some service level agreements measured in days for processes that currently do not have them. For sake of this example, widgets are created a few times a week with a 'start date' and a 'closed date'. I've taken the difference of those dates in PowerQuery to create an aging column. The range is 0 to 1000 for days aged. The average is something like 250 days to work a widget.

What is the best way you can suggest to package this intelligence up to present an argument for establishing SLAs? I binned all of the data into time range buckets (< 30, 30 - 60, 60 - 90, 90 - 120, etc) and figured out that about 60% of the time, widgets are closed in less than 120 days. And the other 40% of the time more than 120 days. The 75th percentile is 206 days and the 50th percentile is 69 days.

I need to use this data or other, more powerful metrics to introduce some reason into an emotional situation, after handling the emotions, to suggest a reasonable SLA. What do you think is a good way to represent this data?

[Anonymous]

Hi @gemcityzach ,
You can try to create a column to group the ids

Group = 
SWITCH(
    TRUE(),
    'Table'[Aging] < 10, "< 10",
    'Table'[Aging] >= 10 && 'Table'[Aging] < 20, "10 - 20"
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Sahir_Maharaj]

Hello @gemcityzach,

I would recommend using a histogram or density plot to show the aging data with clear percentile markers (50th, 75th, 90th) and average. This will make it evident where most of the processing times fall and highlight the tail end of longer durations.

[gemcityzach]

How would you recommend I do that? I have hundreds of start / end dates, some with blank end dates and hundreds of aging observations. There isn't a factor like a "name" or "type" that I can aggregate on.

My data is is like this:

ID	Start Date	Close Date	Aging
1234	12/12/2024	12/24/2024	12
51355	10/01/2024	10/05/2024	4
12234	09/01/2024