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
Hiya, the measure definition is:
ACD handling Av = DIVIDE(SUM('Performance by Agent'[ACD handling time (days)]),SUM('Performance by Agent'[ACD calls handled]))It outputs a decimal fraction of a day:
There are no calc groups left in the model:
I tried opening a fresh new PBIX and pasting a column of decimal values into it, setting the custom format of the column to [hh]:mm:ss in the model view. I got the same result - the format string displayed instead of the values.
I think I've found the answer for you!
So the whole saga: you wanted your duration measures to display as hh:mm:ss in tables but stay as real numbers for charts. We went down a rabbit hole trying model-level format strings ([hh]:mm:ss, then hh:nn:ss), but they kept showing up as literal text in the visual
BUT:
Power BI's time format strings only work on measures that return an actual DateTime, not a plain Decimal. Your measures were decimals (fractional days), so the format engine had nothing to format and just printed the format string back at you.
The fix was to make the measure return a real DateTime instead. You take your fractional-days value and add it to the Power BI epoch date (or any date that works for you), so the result is a proper datetime where the time portion represents your duration.
For example:
ACD handling Av =
VAR _days = DIVIDE(SUM('Performance'[ACD handling time (days)]), SUM('Performance'[ACD calls handled]))
RETURN DATE(1899,12,30) + _days
Then set the measure's format to hh:nn:ss and it just works. Tables show 01:23:45, charts plot it as time, sorts and aggregates work fine.
I have attached a screenshot for you of the experiment I did with one of the measures.If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai KarmaniThe 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
Thank you! The DateTime factor was the information I was missing.
The measure now displays correctly in a table or matrix. However, I can't drop it onto a bar or column chart, and I can't use it for conditional formatting - Power BI doesn't let me select it in either case.
We're so close! Is there a solution to this? Also, what would you recommend for durations over 24 hours? Do I have to resort to individual text-based measures again, or can I use dynamic formats or some other more elegant solution?
That works! Bizarrely though, it's forcing the axis labels to present only one value as a text string:
Do you have a solution for that?
The dynamic format is working fine, the issue is you've put the duration measure on the axis (category) of the bar chart. That's why you see the same 00:06:15 repeated. A formatted measure belongs on the Value / Y-axis (the bar length), not the axis.
Put a real category on the axis instead, like Agent, Team, or Month. Then drop ACD handling Av into the Values well. Now each bar represents a category, the bar length is the duration, and the data labels and value axis show 00:06:15 style correctly.
If you genuinely just want to show one single number with no breakdown, a bar chart is the wrong visual anyway, use a Card. But for any real chart, category on the axis, duration in values.
Thanks. None of the bar or column chart visuals have a "Values" well for me, and they don't let me drop the measure into the X axis. There's no "Values" well in the X axis format dropdown either, but there is in the Data Labels dropdown, and I can drop the measure in there. It's an acceptable workaround I suppose. Thanks for your help!