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
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?
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
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!