Gauge visual showing average duration in hh:mm:ss

[11111111]

I'm new to power bi and im struggling to make this happen if its even possible.

I have data with start and end times, have the difference between end and start time for our wait time. I have that displaying properly as hh:mm:ss and duration data type in query editor and table view but when i try to apply it to a visual all i get are count options no option for Average, Max, Min, etc

The end goal is to have a guage chart with max hard set target to 25mins, dynamic value for average depending on the slicer settings for the date range. min value will be as low as 3mins, and max value can be as high as 1hr 30mins.

[11111111 · Accepted solution]

I appreciate your help @Jai-Rathinavel , @ajaybabuinturi  , and @v-lgarikapat . your solutions weren't what i was looking for but it helped me understand this post link below and that gave me 90% what i was looking for. instead of formatting you leave it as a number multiply each variable so its in the proper spot then hard format the visual so 10 and lower are your seconds, 100 to 1000 are the minutes, and 10000 plus are your hours. doesnt seem like it'll give exact ratios (30mins 3000 won't be half of 1hr 10000) but itll be close enough for what i need.

 

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/td-p/79363...

 

 

[11111111]

Is it possible the gauge chart just isnt compatible with time values and i need to use a different visual?

[v-lgarikapat]

Hi @11111111 ,

Thanks for reaching out to the Microsoft fabric community forum.
@Jai-Rathinavel , @ajaybabuinturi  Thanks for your prompt response

Thanks for your question regarding the Gauge visual in Power BI. You’re right to notice some limitations when working with time values like hh:mm:ss.

Unfortunately, the Gauge visual in Power BI does not support time or duration formats directly. It only works with numeric scalar values, so time values need to be converted into seconds, minutes, or other numeric formats before they can be used.

 Suggested Workaround:

You can convert your time duration into seconds (e.g., using DATEDIFF or TotalSeconds), and use that numeric measure in the Gauge visual. To still show the hh:mm:ss format, you can create a separate measure like this:

AvgDurationFormatted =

VAR TotalSec = [AvgDurationSec]

VAR HH = INT(TotalSec / 3600)

VAR MM = INT(MOD(TotalSec, 3600) / 60)

VAR SS = MOD(TotalSec, 60)

RETURN FORMAT(HH, "00") & ":" & FORMAT(MM, "00") & ":" & FORMAT(SS, "00")

This can then be shown in a Card visual alongside the gauge to provide a more readable representation of the duration.

 Recommended Visual Alternatives:

If you're aiming for a better experience with duration values, you may consider:

• Card Visual for formatted hh:mm:ss
• Bar/Column Chart to compare durations across categories
• Line Chart to show trends over time
• Bullet Chart (custom visual) for actual vs target with more flexibility
• KPI Visual for showing duration against a goal with trend indication

                       Card Visual 

                  Guage Chat

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[Jai-Rathinavel]

Hi @11111111  Please set the Data Type of your column to Time like below

Thanks,

Jai

[11111111]

I have gone to the original data, the query editor section, table view section, report view section, and at one point hard set the visual to only show in hh:mm:ss through visual formatting none worked and hard setting the visual made the values disappear altogether.

[ajaybabuinturi]

Hi @11111111,
Can you follow the below steps.

1. Make sure the start and end date date columns should be date/time format(datatype)
2. Create a calculated column for 'WaitTimeMinutes" as below

WaitTimeMinutes = DATEDIFF('Table'[StartTime], 'Table'[EndTime], MINUTE)​

• Create Measures for Aggregates like Min, Max, Average as below

MinWaitTime = MIN('Table'[WaitTimeMinutes])

MaxWaitTime = MAX('Table'[WaitTimeMinutes])

AvgWaitTime = AVERAGE('Table'[WaitTimeMinutes])​

• If you want your average time then use below measure

AvgWaitInHHMMSS = 
VAR TotalMinutes = [AvgWaitTime]
VAR Hours = INT(TotalMinutes / 60)
VAR Minutes = INT(MOD(TotalMinutes, 60))
VAR Seconds = 0
RETURN
FORMAT(TIME(Hours, Minutes, Seconds), "hh:mm:ss")​

Using above steps you will achive your requirement.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[11111111]

I had tried a regular Average measure Measure = Average(Table[column]) and it wouldnt let it go anywhere but "tooltips" section for the gauge chart, and your more complicated measure that looks to try and hard set the format as hh:mm:ss its doing the same thing i cant place it in the value portion for the visual

[11111111]

I had to adjust the formula a bit to make it work properly, but it only seems to work with Card visuals, every other visual it will only go in the tooltip section