CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
user1274820
user1274820

Reputation: 8144

PowerBI Calculate the Average of a Subgroup of Times

this is probably relatively simple, but I've been having some trouble figuring out how to do this.

I have data that looks like this:

┌──────┬────────────┬──────┬─────────────┐
│ TAID │ AssignedTo │ Type │ ElapsedTime │
├──────┼────────────┼──────┼─────────────┤
│    1 │ Person1    │ A    │       .0008 │
│    2 │ Person1    │ B    │       .0007 │
│    3 │ Person2    │ A    │       .0005 │
│    4 │ Person3    │ A    │         .05 │
│    5 │ Person2    │ A    │       .0654 │
│    6 │ Person1    │ A    │       .0784 │
│    7 │ Person3    │ B    │       .4567 │
└──────┴────────────┴──────┴─────────────┘

I'm supposed to get the average of the elapsed time for each person.

I already have some code that takes the weird PowerBI elapsed time and converts it to HH:mm:ss

TaskElapsedTime = 
IF(SUM('qabintaskbin'[ElapsedTime]) >= 1,
    INT(SUM('qabintaskbin'[ElapsedTime]))*24 + 
    INT(FORMAT(SUM('qabintaskbin'[ElapsedTime]),"hh")) & ":" & 
    FORMAT(SUM('qabintaskbin'[ElapsedTime]),"mm:ss")
  ,
    FORMAT(SUM('qabintaskbin'[ElapsedTime]), "hh:mm:ss")
)

I was looking into how to break the data down and average it and I found something that suggested this:

VAR TheAverage = AVERAGEX(
    KEEPFILTERS(VALUES('qabintaskbin'[AssignedTo])),
    CALCULATE(SUM('qabintaskbin'[ElapsedTime]))
)

Combining it with the formatting code becomes:

VAR TheAverage = AVERAGEX(
    KEEPFILTERS(VALUES('qabintaskbin'[AssignedTo])),
    CALCULATE(SUM('qabintaskbin'[ElapsedTime]))
)
return IF(TheAverage >= 1,
    INT(TheAverage)*24 + INT(FORMAT(TheAverage,"hh")) & ":" & 
    FORMAT(TheAverage,"mm:ss"),
    FORMAT(TheAverage, "hh:mm:ss")
)

But this is just returning the total number it seems?

Test

I would like it to show the average time spent on individual tasks so like the average of all of Person1's elapsed times in the example would be .0266

In writing this out I'm guessing instead of using AVERAGEX on the sum of the numbers, I should instead use DIVIDE by the COUNT of rows or something.

Anyway, if you guys can help me out I'd appreciate it!

Upvotes: 1

Views: 264

Answers (2)

user1274820
user1274820

Reputation: 8144

I think I got it working using this:

ElapsedTimeAvgAssignedTo = 
var TheAverage = 
DIVIDE(
  CALCULATE(
    SUM(qabintaskbin[ElapsedTime]),KEEPFILTERS(VALUES(qabintaskbin[AssignedTo]))
  ),
  CALCULATE(
    COUNT(qabintaskbin[AssignedTo]),KEEPFILTERS(VALUES(qabintaskbin[AssignedTo]))
  ),
  ""
)
return IF(TheAverage >= 1,
    INT(TheAverage)*24 + INT(FORMAT(TheAverage,"hh")) & ":" & 
    FORMAT(TheAverage,"mm:ss"),
    FORMAT(TheAverage, "hh:mm:ss")
)

Basically divide the sum of the elapsed time filtered by the AssignedTo by the count of those rows to get the average, then convert it to hh:mm:ss.

The average time for someone who did two services - one in 7 minutes, one in 13 minutes comes to approximately 10 minutes:

Output

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40244

Ignoring the formatting aspect, you should be able to average like this:

AvgTime =
    CALCULATE (
        AVERAGE ( 'qabintaskbin'[ElapsedTime] ),
        ALLEXCEPT ( 'qabintaskbin', 'qabintaskbin'[AssignedTo] )
    )

Upvotes: 1

Related Questions