How to display Maximum Time Value per Metric in a Power BI Column Chart

How to display Max Time Value of each Metric on a Column Chart in Power BI

Scenario:

Suppose we have a dataset where the Metric_Value column contains DateTime values for each Metric_Name, as shown below.

From this dataset, we need to extract the time component from Metric_Value and convert it into a numeric format so it can be used to display properly on a clustered column chart.

The refined Dataset with Time Values is as follows:

let

    SampleData = 

    #table(

        {"Metric_Id", "Metric_Name", "Metric_Value", "Metric_DataType"},

        {

            {1, "JobRunDateTime", #datetime(2023, 5, 10, 14, 30, 00), "DateTime"},

            {2, "JobRunDateTime", #datetime(2024, 1, 15, 21, 45, 30), "DateTime"},

            {3, "JobStartTime", #time(9, 45, 15), "Time"},

            {4, "JobStartTime", #time(14, 30, 10), "Time"}

        }

    ),

// Convert Time to duration:

_Duration = Table.AddColumn(

        SampleData,

        "TimeAsDuration",

        each #duration(

                0,

                Time.Hour([Metric_Value]),

                Time.Minute([Metric_Value]),

                Time.Second([Metric_Value])

            ),

        type duration

    ),

// Convert duration to total seconds (numeric):

_TotalSeconds = Table.AddColumn(

        _Duration,

        "TimeAsSeconds",

        each Duration.TotalSeconds(

            #duration(

                0,

                Time.Hour([Metric_Value]),

                Time.Minute([Metric_Value]),

                Time.Second([Metric_Value])

                )

            ),

        type number

    ),

// Convert Time into Hour.MinuteSeconds numeric format with padded Seconds:

_NumericTime = Table.AddColumn(

        _TotalSeconds,

        "TimeAsNumber",

        each Number.Round(

            Number.FromText(

            Text.From(Time.Hour([Metric_Value])) & "." &

            Text.PadStart(Text.From(Time.Minute([Metric_Value])),2,"0") &

            Text.PadStart(Text.From(Time.Second([Metric_Value])),2,"0")

            ), 

        4),

        type number

    )

in

    _NumericTime

The final formatted dataset includes a TimeAsNumber column, which represents the numeric conversion of time values. We will use this column to generate a clustered column chart that displays the maximum time value for each metric.

The TimeAsDuration (d.HH:mm:ss) and TimeAsSeconds columns were created for reference purposes only and are not used further in this example.

We will use the below measure to format the TimeAsNumber to Time (HH:mm:ss) to display on the Chart for the Data Labels.

Format TimeLabel =

VAR TimeValue = MAX(tbl_Metrics_TimeValues[TimeAsNumber])   -- E.g. 9.4515

VAR Hrs   = INT(TimeValue)                             -- Hours

VAR Mnts_Sec  = ROUND((TimeValue- Hrs) * 10000,0)      -- Minutes+Seconds

VAR Mnts   = INT(DIVIDE(Mnts_Sec,100))                 -- Minutes

VAR Sec   = MOD(Mnts,100)                              -- Seconds

RETURN

FORMAT(Hrs,"0") & ":" & FORMAT(Mnts,"00") & ":" & FORMAT(Sec,"00")

Generate the Clustered Column Chart:
We will use the Metric Name on the X-Axis and TimeAsNumber column on Y-axis to generate the Clustered Column chart with Max value for each Metric.

Since the purpose is not to display the Time as Number, hide the Y‑axis values, the Y‑axis title, and the tooltips.
Next, in the Data Labels > Values section of the chart formatting options, assign the measure Format TimeLabel Value (created earlier) so that the actual metric Time values are displayed on columns instead of the number.

We can use the below measure to format the Columns of the Chart.

Metric ColorFormat =

SWITCH(

    SELECTEDVALUE(tbl_Metrics_TimeValues[Metric_Name]),

    "JobRunDateTime",    "#1E90FF",    -- Azure Blue

    "JobStartTime",      "#4682B4",    -- SteelBlue

    "#808080"         -- Gray (default color)

    )

Result:

The final output is a clustered column chart with Metric_Name on the X‑axis and metric Max time values shown as data labels.

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------