Query re: average time

[Starbug]

Hello, I have a table named "Incidents" and a column named totalDuration_inSeconds, will the following when applied give me the average time of the total incidents? including when i apply extra filters such as region the incident was created?

Measure = FORMAT(AVERAGE(Incidents[totalDuration_inSeconds]),"HH:MM:SS")

[Anonymous · Accepted solution]

Hi @Starbug,

You can create one measure as below:

averageDuration = var ave=AVERAGE('Incidents'[totalDuration_inSeconds])

var h = if(LEN(INT(ave/3600))=1,CONCATENATE("0",INT(ave/3600)),CONCATENATE("",INT(ave/3600)))

var m = if(LEN(INT(MOD(ave,3600)/60))=1,CONCATENATE("0",INT(MOD(ave,3600)/60)),CONCATENATE("",INT(MOD(ave,3600)/60)))

var s = if(LEN(MOD(MOD(ave,3600),60))=1,CONCATENATE("0",left(MOD(MOD(ave,3600),60),2)),CONCATENATE("",left(MOD(MOD(ave,3600),60),2)))

var duration=CONCATENATE(h,CONCATENATE(":",CONCATENATE(m,CONCATENATE(":",s))))

return  duration

Best Regards

Rena

[TomMartens]

Hey @Starbug ,

I guess this will provide what you are looking for:

https://community.powerbi.com/t5/Desktop/Average-duration-in-dd-hh-mm-ss/td-p/313661

Regards,

Tom

[Starbug]

Sorry just to add, the data appears like this if it helps at all; 

https://i.imgur.com/gwCqQ3v.png