Average duration not calculated correctly

[shado26]

Dear All

need your assist to check why the average are not calculated correctly 

im using below DAX to calculate duration 

Time Diff2 = VAR PreviousTime=TOPN(1,Filter('SOP-Log','SOP-Log'[Orderno]=earlier('SOP-Log'[Orderno])&&'SOP-Log'[LogDatetime]<earlier('SOP-Log'[LogDatetime])),[LogDatetime],desc)
RETURN
DATEDIFF(MINX(PreviousTime,'SOP-Log'[LogDatetime]),'SOP-Log'[LogDatetime],SECOND)

and below to show the total duration as Day / HRs / Mins / Secs

Duration = 
VAR TotalSeconds=SUM('SOP-Log'[Time Diff2])
VAR Days =INT(TotalSeconds/60/60/24)
VAR Hours = mod(INT(TotalSeconds/60/60),24)
VAR Mins =MOD(INT(TotalSeconds/60),60)
VAR Secs = MOD(TotalSeconds,60)
return 


days &" Days " & hours & " Hrs " & Mins & " Mins " & Secs & " Secs")

and it show correct result this order are take 100 day 

this the Action duration for the above 100 day 

Action Duration 
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 24 minutes 43 seconds
0 days 0 hours 43 minutes 44 seconds
0 days 0 hours 4 minutes 1 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 12 minutes 49 seconds
0 days 0 hours 43 minutes 25 seconds
0 days 0 hours 1 minutes 30 seconds
0 days 19 hours 31 minutes 45 seconds
0 days 1 hours 56 minutes 11 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 55 seconds
0 days 0 hours 13 minutes 19 seconds
2 days 3 hours 32 minutes 53 seconds
3 days 17 hours 25 minutes 47 seconds
0 days 0 hours 0 minutes 54 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
2 days 4 hours 21 minutes 17 seconds
0 days 0 hours 12 minutes 41 seconds
13 days 16 hours 24 minutes 1 seconds
5 days 1 hours 6 minutes 51 seconds
0 days 0 hours 0 minutes 16 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 23 seconds
0 days 0 hours 1 minutes 1 seconds
0 days 0 hours 1 minutes 6 seconds
0 days 0 hours 0 minutes 51 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 38 seconds
0 days 1 hours 16 minutes 12 seconds
24 days 6 hours 1 minutes 18 seconds
0 days 0 hours 0 minutes 15 seconds
0 days 0 hours 1 minutes 24 seconds
0 days 0 hours 0 minutes 53 seconds
0 days 0 hours 0 minutes 48 seconds
0 days 0 hours 0 minutes 0 seconds
42 days 17 hours 27 minutes 42 seconds
0 days 0 hours 0 minutes 11 seconds
0 days 0 hours 0 minutes 16 seconds
0 days 1 hours 10 minutes 43 seconds
0 days 0 hours 0 minutes 46 seconds
0 days 0 hours 0 minutes 32 seconds
0 days 0 hours 0 minutes 52 seconds
0 days 0 hours 1 minutes 17 seconds
0 days 0 hours 0 minutes 27 seconds
0 days 0 hours 0 minutes 22 seconds
0 days 0 hours 0 minutes 29 seconds
0 days 0 hours 0 minutes 34 seconds
0 days 0 hours 0 minutes 28 seconds
0 days 0 hours 0 minutes 27 seconds
0 days 0 hours 0 minutes 30 seconds
0 days 0 hours 2 minutes 2 seconds
0 days 0 hours 0 minutes 33 seconds
0 days 0 hours 0 minutes 12 seconds
4 days 23 hours 23 minutes 58 seconds
0 days 0 hours 2 minutes 25 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 0 minutes 0 seconds
0 days 0 hours 21 minutes 37 seconds
0 days 0 hours 0 minutes 27 seconds
0 days 4 hours 25 minutes 54 seconds
0 days 0 hours 6 minutes 27 seconds
0 days 0 hours 25 minutes 42 seconds

but when i use average it show not correctly value how 100 day the average is 1 day 

AVG Duration = 
VAR TotalSeconds=AVERAGE('SOP-Log'[Time Diff2])
VAR Days =INT(TotalSeconds/60/60/24)
VAR Hours = mod(INT(TotalSeconds/60/60),24)
VAR Mins =MOD(INT(TotalSeconds/60),60)

return 

days &" Days " & hours & " Hrs " & Mins & " Mins " )

can you help me to fix this 

below are the original DAX 

Sales Order Process " time for each action "

[v-yuta-msft]

Hi shado26,

"but when i use average it show not correctly value how 100 day the average is 1 day"

<--- What's you expected result? Could you clarify more details about your logic?

Regards,

Jimmy Tao

[shado26]

Dear @v-yuta-msft

if we take 3 duration time as EX the average should be  26 day

Action Duration 
13 days 16 hours 24 minutes 1 second
24 days 6 hours 1 minutes 18 seconds
42 days 17 hours 27 minutes 42 se

[shado26]

Any luck

[shado26]

Please let me know if you have any help you can give me