Is this the right way to calculate the Average Time to Resolve a Case?

[ninos-shiba]

I've built a Power App time tracker for employees that work on cases. The data is stored in a SharePoint List and looks like this:

Case	Start Time	Finish Time	Total Time Spent (hours)
1234	7:00 AM	9:00 AM	2
1234	10:00 AM	12:00 PM	2
9999	1:30 PM	4:30 PM	3

Before, I was just taking the AVERAGE of the Total Time Spent column, but the number was very low. I think I may have to divide by a distinct count of the case number, something like this:

Average Time per Case (min) = (SUM(Records[Time Spent (mins)]) / DISTINCTCOUNT(Records[Case Number])) + 0

Is that correct?

[Anonymous]

This is more readable but does the same thing (if you want blank to be 0, add 0 like you have in your formula):

// This measure is the counterpart of a measure
// such as, for instance, [Total Manhours] where
// you sum up time of all the workers, so in a day
// you could have more than 24 hours.
[Total Casetime] = sum( Records[Time Spent (mins)] )

[Avg Time per Case] = // a measure, not a calculated column
averagex(
    distinct( Records[Case] ),
    [Total Casetime]
)

[ninos-shiba]

Using that formula, the number jumped up from 107 minutes to 87,000 minutes:

Average Time per Case (min) = AVERAGEX(DISTINCT(Records[Case Number]), SUM(Records[Time Spent (mins)]) + 0)

Did I do something wrong?

[Anonymous · Accepted solution]

@ninos-shiba 

What MY formula does is this:

1. In the current context it looks for different values of Case.

2. Then, for each such Case it calculates the sum of Time Spent (mins). Meaning, it adds up all the values for this very Case.

3. It averages these sums.

Your formula is plain wrong as it does not effect context transition. Check out my formula and you'll see they calculate 2 different things. Your formula must wrap SUM(...) in CALCULATE to be similar to mine.

The outcome depends on what you have in your table and if the records that belong to the same Case are non-overlapping periods. If they do overlap, then this formula will overstate the time. However, I can see from the sample table of yours that the periods are non-overlapping.

By the way, it really matters where you add the "+ 0" bit. If you do it under the AVERAGEX function, then you'll also count in the Cases that have no Time Spent (mins). This will most likely understate the figure. You should, I think, add the 0 to outside the formula: AVERAGEX(...) + 0.

If the above does not explain everything, it means you have not shown us everything about your model.