Forum Discussion
Durations - Dynamic Formats or Calculation Groups? Or both?
The fix is a dynamic format string. Revert the measure to plain numeric:
ACD handling Av =
DIVIDE(SUM('Performance'[ACD handling time (days)]), SUM('Performance'[ACD calls handled]))Then select it, Measure tools ribbon, set Format to Dynamic, and paste this into the format expression:
VAR Seconds = INT ( SELECTEDMEASURE () * 86400 )
VAR Hrs = INT ( Seconds / 3600 )
VAR Mins = INT ( MOD ( Seconds, 3600 ) / 60 )
VAR Secs = MOD ( Seconds, 60 )
RETURN
"""" & FORMAT ( Hrs, "00" ) & ":" & FORMAT ( Mins, "00" ) & ":" & FORMAT ( Secs, "00" ) & """"The `""""` wraps the time as literal text while keeping the real number underneath. Now it works everywhere: tables, charts with formatted labels, conditional formatting, and over-24h shows as 25:30:00 with no rollover.
For your seven measures, paste it into each one, or set up one calculation group with this as the Format String Expression and SELECTEDMEASURE() as the expression. I'd do per-measure first to get it working, then tidy up with a calc group later if you want.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
Hey,I'll try to help 🙂
the trick nobody tells you: Power BI's [hh]:mm:ss format treats the underlying number as days, just like Excel.
Your FORMAT(SELECTEDMEASURE()...) attempt failed because a dynamic format string expression needs to return the format pattern itself (the literal "[hh]:mm:ss"), not a pre-formatted string. You were handing the engine "01:30:00" when it was asking "what pattern should I apply?"
Just do this:
Avg Handle Time = DIVIDE(SUM(fct_interactions[duration_seconds]), [Interaction Count]) / 86400
Set the format string on the measure to [hh]:mm:ss (type it in directly, it's not in the dropdown). Tables show 01:23:45, charts plot the numeric value, axes also render as durations. Everything just works.
That's it. Calculation groups are a great tool but you don't need one here. Reach for them only if you genuinely need the raw seconds preserved somewhere (like a "Raw Seconds" vs "As Duration" toggle), and even then watch out: calc groups apply to every measure in scope, so non-duration measures pick up the formatting too unless you guard with SELECTEDMEASURENAME().
Your proportion measures are a separate concern. They're unitless 0 to 1, format them to 0.0% and move on.
divide by 86400 and set the format string.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
Thank you for the quick and helpful response! Sorry I'm missing something obvious - where do I enter the format string [hh]:mm:ss? I tried entering it as a dynamic format in quotation marks, "[hh]:mm:ss", and as a custom format without quotation marks in the "Data format" menu on the visual, but predictably that just showed the format string as text. Where does it go?
