Show time on y axis line chart

[soumyaiyer]

@speedramps

Hi, 

I am trying to show time (HH:MM:SS) in the line chart on y-axis, but BI shows it as count. I changed the format under the modelling tab but no changes. Need help please. Thanks!

[soumyaiyer]

Thank you! I have another question. I will create new request and mention you !

[MasonMA · Accepted solution]

Hello @soumyaiyer 

Power BI does not support Time (hh:mm:ss) as a continuous numeric value on the Y-axis of a line chart. It defaults to some kind of Aggregation (like COUNT) of time-based columns, and often treats Time values as text or datetime, not durations. But you may convert time durations into numeric values for plotting, and then format them back into hh:mm:ss if you want. 

Here's a simple demo with some random data to give you some ideas

Create a Column to convert your hh:mm:ss to Seconds

AHT_Seconds = 
HOUR('Table'[Avg Handle Time]) * 3600 +
MINUTE('Table'[Avg Handle Time]) * 60 +
SECOND('Table'[Avg Handle Time])

Create one explicit Measure for your Y-Axis values instead of using implicit measures from Power BI like COUNT. (use CALCULATE in Iterator function so correct context transition happens)

Avg_Sec = 
AVERAGEX(
    VALUES('Table'[Day]),
    CALCULATE(AVERAGE('Table'[AHT_Seconds]))
)

Idealy you can also create one Tooltip for hh:mm:ss to show on your chart. 

AHT_ToolTip=
VAR TotalSeconds = [Avg_Sec]
VAR HH = INT(TotalSeconds / 3600)
VAR MM = INT(MOD(TotalSeconds, 3600) / 60)
VAR SS = MOD(TotalSeconds, 60)
RETURN FORMAT(HH, "00") & ":" & FORMAT(MM, "00") & ":" & FORMAT(SS, "00")

Hope this helps:)