Forum Discussion
Solved
AverageX with Calculated Column and Format function
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
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.
Many thanks, Sahir!
2 Replies
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.
Many thanks, Sahir!