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
Can you please check two things for me:
The measure definition. Click on ACD handling Av and look at the formula bar. Is it something normal like:
DIVIDE(SUM(table[acd_seconds]), [ACD calls handled]) / 86400Or is it something weird like:
"[HH]:mm:ss"or wrapped in a FORMAT() that returns text? If it's returning text, that's your problem. Fix the DAX.
Also: Any other calculation groups still in the model. Expand the calc group node in your screenshot. You deleted one but check there is there another one? or that the one shown ("Calculation group" with item "S to HHMMSS") isn't actually still there. If so, nuke it properly: right-click the group itself, delete, then Save the model.
If tou want, paste the DAX for ACD handling Av here
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 KarmaniThank 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?