concatenation - Excel / DAX Averaging Issue -

thank taking time @ question.

dax doesn't allow durational values, in order work out average value list of durational values, i've split durations component 'hour', 'minute' , 'second' values. each of these have been averaged , concatenated of workaround.

here's confusion:

in excel, when divide duration-in-seconds value 86400 (and consequently format in [h]:mm:ss), individual values tally precisely (e.g. 10:49:12 derived duration-in-seconds value , individual 'hour', 'minute' , 'second' values match - 10, 49 , 12 respectively) - yet when average duration-in-seconds column, receive different value of averaging component time values , concatenating them (separated colon).

i'm sure simple, i'm hoping 1 of great minds on here able put me out of misery , provide concise explanation...it's tiny mind is; sleep deprivation notwithstanding!

thank in advance,

rob

i have no issue replicating calculations in power bi using dax. can check if dax same yours.

table 100 rows of random data:

durationinseconds:

durationinseconds = randbetween(0, 150000) 

hour:

hour = quotient('time'[durationinseconds], 3600) 

minute:

minute = mod(quotient('time'[durationinseconds], 60), 60) 

second:

second = mod('time'[durationinseconds], 60) 

averagebydurationinseconds:

averagebydurationinseconds = average('time'[durationinseconds]) 

averagebyhourminuteseconds:

averagebyhourminutesecond = average('time'[hour]) & ":" & average('time'[minute]) & ":" & average('time'[second]) 

results:

of course measure averagebyhourminuteseconds won't make sense because decimal places must retained accuracy, calculation still valid. (21.9 * 3600 + 29.73 * 60 + 29.77 = 80653.57)