AverageX with Calculated Column and Format function

[stvn43]

I created a calculated column to get a time duration value using the Format function, and it looks great, but I can't use AverageX on it because now it's a string value. Is there a way to get this formatting w/out using the Format function to keep the output value to a time-based value?

Here is my DAX for the calculated column and the assoicated mesaure:

Difference =

VAR EditTime =

  IF([editbegintime] <= 0, "00:00:00",

    FORMAT(TimeToEdit[editendtime] - TimeToEdit[editbegintime],"HH:MM:SS"))

Return

EditTime

Time to Caption =

VAR TTC =  

    AVERAGEx(FILTER(TimeToEdit, [service] = "Captioning"), [Difference])

Return

TTC

[Sahir_Maharaj · Accepted solution]

Hello @stvn43,

You can use the TIME function in DAX to create a duration value instead of using the FORMAT function.

Difference = 
VAR EditTime =
  IF([editbegintime] <= 0, TIME(0, 0, 0),
    TimeToEdit[editendtime] - TimeToEdit[editbegintime])
RETURN EditTime

Time to Caption =
VAR TTC = AVERAGEX(FILTER(TimeToEdit, [service] = "Captioning"), [Difference])
RETURN FORMAT(TTC, "HH:MM:SS")

By using the TIME function, you can now use the AverageX function on the Difference column because it returns a duration value instead of a string.

I hope this helps! Let me know if you have any further questions.

[stvn43 · Accepted solution]

Many thanks, Sahir!

[Sahir_Maharaj · Accepted solution]

Hello @stvn43,

You can use the TIME function in DAX to create a duration value instead of using the FORMAT function.

Difference = 
VAR EditTime =
  IF([editbegintime] <= 0, TIME(0, 0, 0),
    TimeToEdit[editendtime] - TimeToEdit[editbegintime])
RETURN EditTime

Time to Caption =
VAR TTC = AVERAGEX(FILTER(TimeToEdit, [service] = "Captioning"), [Difference])
RETURN FORMAT(TTC, "HH:MM:SS")

By using the TIME function, you can now use the AverageX function on the Difference column because it returns a duration value instead of a string.

I hope this helps! Let me know if you have any further questions.