Hello all - I am back 🙂 I have a matrix table where I am summing time duration through a measure(also shown). My issue is anything over 24 hours is not subtotaling or totaling correctly. I have tried this so many ways, but can't figure out to make it work. Any help would be greatly appreciated!
Maybe you can try this:
I create a sample date:
Then create a measure:
Measure =
CONCATENATE(
DATEDIFF(
DATE(1899, 12, 30),
SUM('Table (2)'[time]),
HOUR
),
CONCATENATE(
":",
FORMAT(
SUMX(
ALLSELECTED('Table (2)'),
'Table (2)'[time]
),
"nn:ss"
)
)
)The result is as follow:
Best Regards,
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@YcnanPowerBI , Instead of that on sum measure use new Dynamic Format Strings for Measures
example - of format
"""" & QUOTIENT(SELECTEDMEASURE(), 86400) & "." & format( QUOTIENT(MOD(SELECTEDMEASURE(), 86400),3600),"00") & ":" & format(QUOTIENT(MOD(MOD(SELECTEDMEASURE(), 86400),3600),60), "00")& ":" & format(Round(MOD(MOD(MOD(SELECTEDMEASURE(), 86400),3600),60),0),"00")
File is attached with example
Power BI Dynamic Format Strings for Measures| Format without converting to text
