Forum Discussion

soumyaiyer's avatar
soumyaiyer
Helper IV
1 year ago
Solved

Show time on y axis line chart

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. Thank...
  • MasonMA's avatar
    1 year ago

    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:)