Average Time Duration in PowerBI Matrix-CodePudding

Is anyone aware of a way in which one can calculate an average time duration in hh:mm:ss format in a matrix visual in PowerBI?

In my table, I have these Date/Time Opened and Date/Time Closed fields.

I created a new column called Time to Close by subtracting Date/Time Closed from Date/Time Opened.

I am at a complete loss as to how I can depict an average of these time durations in the HH:mm:ss format by adding the Time to Close field as a value on a matrix visual because it's only presenting the data in a hierarchical manner.

I've tried converting to the number of seconds and running an average on that, but again it still only shows average seconds, not the HH:mm:ss format that I need.

Has anyone had issues with this before? I can get everything exactly how I need it in my table, but not in the Matrix visual. I've tried a number of DAX measures, and I'm still striking out.

Any help or nudge in the right direction would be much appreciated.

CodePudding user response：

Does this solves the case?

FORMAT(
      TIME ( 0, 0, varAvrSeconds)
      ,"HH:MM:SS"
)

CodePudding user response：

I think Mik's answer is almost there, but the string formatting rules of Power BI's FORMAT() function are slightly different: https://docs.microsoft.com/en-us/dax/format-function-dax

The below should work, where varAvrSeconds is your measure (or variable) producing the correct answer in seconds:

FORMAT(
      TIME ( 0, 0, varAvrSeconds)
      ,"hh:nn:ss"
)