How format Average Time in Power BI: Converting 60 Seconds to 1 Minute

[AT150745]

Hi everyone,

I'm working with time data in Power BI and need some help with formatting the average time correctly. Specifically, I want to ensure that when calculating the average, 60 seconds are automatically converted to 1 minute.

For example, I calculated the average of seconds from my data, and it currently displays as 95.00 seconds, as shown 

Instead of showing the result as 95.00 seconds, I want it to display in the format of minutes and seconds (e.g., 1:35 for 1 minute and 35 seconds).

Could someone guide me on how to achieve this in Power BI? Here's the DAX formula I tried:

Thanks in advance for your help!

[tharunkumarRTK · Accepted solution]

@AT150745 

I am assuming your seconds column is a numeric column. You can try this pattern:

TotalTime = 
Var __TotalSeconds = SUM('Time'[Seconds])
Var __Minutes = INT( DIVIDE(__TotalSeconds , 60)) 
Var __Seoncds = MOD(__TotalSeconds,60)
Var Result = __Minutes & ":" & __Seoncds
RETURN Result 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[DataNinja777 · Accepted solution]

Hi @AT150745 

Please try the measure below:

FormattedTime = 
VAR TotalSeconds = [average]
VAR Minutes = INT(TotalSeconds / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN 
    FORMAT(Minutes, "0") & ":" & FORMAT(Seconds, "00")

 Best regards,

[tharunkumarRTK · Accepted solution]

@AT150745 

I am assuming your seconds column is a numeric column. You can try this pattern:

TotalTime = 
Var __TotalSeconds = SUM('Time'[Seconds])
Var __Minutes = INT( DIVIDE(__TotalSeconds , 60)) 
Var __Seoncds = MOD(__TotalSeconds,60)
Var Result = __Minutes & ":" & __Seoncds
RETURN Result 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!