Sum up the difference between two times as a numeric value

[browna6]

Hi

I have sample data where i am trying to summarise by Area and show the minimum time and the maximum time in each area. Then show the difference between to two times to give me the Total Hours/Minutes between the two times.

I then what to sum up each line to give the total number of hours - which enable me to show the average number of lines per hour. Which in the end, i am hoping to also show the Average number of lines hour 

How is that possible? It keeps just showing me as a total the minimum time (08:38:51) and the max time (19:51:29) and the different between the two - total of 11.21 instead of the sum difference which is 18.19

I have also added below a table which i think (hope) the results should be

User	Date	Time	Area
User A	20230915	84534	CL00
User A	20230915	91509	CL00
User A	20230915	91101	CL00
User A	20230915	84519	CL00
User A	20230915	83851	CL00
User A	20230915	84055	CL00
User A	20230915	91842	CL00
User B	20230915	104802	MZP0
User B	20230915	195129	MZP0
User B	20230915	174031	MZP0
User C	20230915	175535	MZP0
User C	20230915	173102	MZP0
User D	20230915	171908	CS00
User D	20230915	173202	CS00
User D	20230915	91518	CS00
User D	20230915	91443	CS00
User E	20230915	93006	PTP0
User E	20230915	93334	PTP0
User E	20230915	94057	PTP0

Area	Min. of Time	Max. of Time	Difference	Hours	Number Of Lines	Average
CL00	08:38:51	09:18:42	0.027674	0.664167	7	0.094881
CS00	09:14:43	17:32:02	0.345359	8.288611	5	1.657722
MZP0	10:48:02	19:51:29	0.377396	9.0575	4	2.264375
PTP0	09:30:06	09:40:57	0.007535	0.180833	3	0.060278
Grand Total	08:38:51	19:51:29	11:12:38	18.19111	19	0.957427

[Anonymous · Accepted solution]

Hi  @browna6 ,

You can try the following measure：

Measure =
var _table=SUMMARIZE('Table','Table'[Area],"Value",[Hours])
return
IF(
    HASONEVALUE('Table'[Area]),[Hours],SUMX(_table,[Value]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@browna6 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8