How to divide second with whole number and return type should be in HH:MM:SS

[Uzi2019]

Hi Community,

I have two columns one is "Session_Duration" which is coming in terms of SECONDS and another column is "Visits" which is basically a whole number. My requirement is to divide Session_Duration with Visits and format of this answer should be in "HH:MM:SS". 

Any lead would be appreciated.

 

[amitchandak]

@Uzi2019 ,

You have to do the calculation in second then convert it back like

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

[Uzi2019]

@amitchandak 

I am getting wrong answers. please give me query that would solve my question.

[amitchandak · Accepted solution]

New Column

Time  = 
var vSeconds=table[session_duration]table/[visits]
var vMinutes=QUOTIENT( vSeconds,60)
var vRemainingSeconds=round(MOD(vSeconds, 60),0)
var vHours=QUOTIENT(vMinutes,60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=QUOTIENT(vHours,24)
var vRemainingHours=MOD(vHours,24)
return 
  vDays&"."&
  vRemainingHours&":"&
  vRemainingMinutes&":"& 
  vRemainingSeconds& ""
  

New Measure

Time  = 
var vSeconds=Sum(table[session_duration])/sum(table[visits])
var vMinutes=QUOTIENT( vSeconds,60)
var vRemainingSeconds=round(MOD(vSeconds, 60),0)
var vHours=QUOTIENT(vMinutes,60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=QUOTIENT(vHours,24)
var vRemainingHours=MOD(vHours,24)
return 
  vDays&"."&
  vRemainingHours&":"&
  vRemainingMinutes&":"& 
  vRemainingSeconds& ""