Forum Discussion
Sum up the difference between two times as a numeric value
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 |
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.
2 Replies
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/n4TYhF2ARe8Hi 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.
