Matrix Subtotals with HH:MM:SS over 24 HHs

[YcnanPowerBI]

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!  

[Anonymous]

Hi @YcnanPowerBI 

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.

[amitchandak]

@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

https://youtu.be/qHALucET7N8