Finding Process times outside of the Avg

[mcornfield]

I have data that shows a request creation and completion Date/Time. I am looking to find outliers that take longer than average to complete. So I have 2 questions.

1. I am currently subtracting the Completion Date and the Creation date, then looking for the average. My plan is to just subtract the average from the actual and see what the biggest outliers are. I can't seem to figure out how to show the Total Average across all the rows; it keeps breaking it out. How can I do this?
2. Is there a better way all together? Maybe I am thinking about this wrong.
3. king about this wrong.

[v-jingzhang]

Hi @mcornfield 

Do these replies help solve your problem? If so, kindly Accept the appropriate reply as the solution. More people will benefit from it. If you are still confused about it, please provide more details about your problem. Thanks.

Regards,
Jing

[v-jingzhang]

Hi @mcornfield 

There is no Duration data type in Power BI Desktop, but there is a Duration type in Power Query Editor.

You can calculate the duration in Query Editor by ( [CompletionDate] - [CreationDate] ) and change it into Duration type. Then apply this query into Power BI Desktop, and the Duration type will automatically be converted into a decimal number. As a Decimal Number type it can be added or subtracted from a Date/Time field as well as used to calculate the Average/Sum... and so on.

In Query Editor:

In Power BI Desktop, if you want to show the Total Average across all rows, you can create below measure to get the result. You can use this measure to get the difference between actual value and average value.

Avg = AVERAGEX(ALL(Data),Data[Duration])

For example:

Here is a PBIX file for your reference.

Regards,
Community Support Team _ Jing Zhang
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous · Accepted solution]

Hi, there are many ways, but if you are getting confused between time and duration (basically your average gives you duration calculated based on date/time), then to avoid confusion, you could convert the date/time to decimal numbers and then perform your calculations like average duration, outliers, etc...

Internally, date/time is represented as decimal numbers, so if you take the difference between start and end times, it will give you the duration and the average, outliers can be calculated easily.

Just keep the following in mind.

1) Convert the date/time values to decimal numbers.

2) The integer part of the decimal number represents the number of days.

3) The decimal part/fractional part represents the time.

4) 1 hour = 1 day divided by 24

5) 1 minute = 1 day divided by (24*60)

6) 1 second = 1 day divided by (24*60*60)

For example.

18th Feb 2021 10:23:47 AM is represented as 44245.4331828704.

In 44245.4331828704 is the sum of this

44245 days since the beginning of calendar + 10*(1/24) + 23*(1/(24*60)) + 47*(1/(24*60*60)) = 44245.4331828704

So if you take the difference between any two decimal numbers, it will give you the duration.

Take the average of these durations.

Then for each line item, if you take the difference between the average and the duration of that line, it will give you the dispersion from the average. Based on the values of this dispersion, you can find the outliers.