Forum Discussion
Summarise time
I am looking for measure based on SUM of Total Scans/total Time. But here, Total time for the day will be calculated based on SUM of MIN and Max time of all individual SO in that day.
Below table gives a sample dataset of only one day, but there will be data everyday.
Example- SO00307980 started at 15:58 and finished at 17:20, so only this amount of time will be calculated + SO00308243 min max+ etc etc.
| Timestamp | Date | SO | Total Scan |
| 3/12/2021 17:20 | 3-Dec-21 | SO00307980 | 1 |
| 3/12/2021 17:19 | 3-Dec-21 | SO00307980 | 1 |
| 3/12/2021 17:18 | 3-Dec-21 | SO00307980 | 1 |
| 3/12/2021 17:17 | 3-Dec-21 | SO00307980 | 1 |
| 3/12/2021 17:11 | 3-Dec-21 | SO00307980 | 1 |
| 3/12/2021 16:00 | 3-Dec-21 | SO00307980 | 3 |
| 3/12/2021 15:58 | 3-Dec-21 | SO00307980 | 1 |
| 3/12/2021 14:17 | 3-Dec-21 | SO00308243 | 3 |
| 3/12/2021 14:16 | 3-Dec-21 | SO00308243 | 3 |
| 3/12/2021 14:15 | 3-Dec-21 | SO00308243 | 3 |
| 3/12/2021 14:15 | 3-Dec-21 | SO00308243 | 1 |
| 3/12/2021 14:14 | 3-Dec-21 | SO00308243 | 1 |
| 3/12/2021 14:02 | 3-Dec-21 | SO00308245 | 3 |
| 3/12/2021 14:02 | 3-Dec-21 | SO00308245 | 3 |
| 3/12/2021 13:53 | 3-Dec-21 | SO00308245 | 2 |
| 3/12/2021 13:45 | 3-Dec-21 | SO00308245 | 3 |
| 3/12/2021 13:44 | 3-Dec-21 | SO00308245 | 3 |
| 3/12/2021 14:47 | 3-Dec-21 | SO00308246 | 3 |
| 3/12/2021 14:47 | 3-Dec-21 | SO00308246 | 3 |
| 3/12/2021 14:37 | 3-Dec-21 | SO00308246 | 3 |
| 3/12/2021 14:24 | 3-Dec-21 | SO00308246 | 3 |
| 3/12/2021 14:22 | 3-Dec-21 | SO00308246 | 3 |
| 3/12/2021 14:19 | 3-Dec-21 | SO00308246 | 3 |
| 3/12/2021 15:11 | 3-Dec-21 | SO00308281 | 3 |
| 3/12/2021 15:03 | 3-Dec-21 | SO00308281 | 3 |
| 3/12/2021 14:59 | 3-Dec-21 | SO00308281 | 3 |
| 3/12/2021 14:44 | 3-Dec-21 | SO00308281 | 3 |
| 3/12/2021 14:42 | 3-Dec-21 | SO00308281 | 3 |
| 3/12/2021 14:42 | 3-Dec-21 | SO00308281 | 1 |
| 3/12/2021 14:41 | 3-Dec-21 | SO00308281 | 2 |
| 3/12/2021 14:40 | 3-Dec-21 | SO00308281 | 1 |
| 3/12/2021 9:44 | 3-Dec-21 | SO00308282 | 3 |
| 3/12/2021 9:43 | 3-Dec-21 | SO00308282 | 3 |
| 3/12/2021 9:42 | 3-Dec-21 | SO00308282 | 3 |
| 3/12/2021 9:42 | 3-Dec-21 | SO00308282 | 3 |
| 3/12/2021 9:40 | 3-Dec-21 | SO00308282 | 3 |
| 3/12/2021 9:39 | 3-Dec-21 | SO00308282 | 3 |
| 3/12/2021 9:38 | 3-Dec-21 | SO00308282 | 3 |
Hi learner03 refer to attached. The output is as per below.
Let me know if you want me to run you through the above.
The Total Scans measure counts each record by the SO group.
Total Scans = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' ,'Table'[SO] ) )
The division is both by Scan Rate Mins and Scan Rate Secs and it's just the Output Mins or Output Secs divided by the Total Scans.
All the best!
Theo 🙂
8 Replies
Hi learner03
Please see attached file for below output:
You will need to create the following measures:
Duration =
VAR _Min = CALCULATE ( MIN ('Table'[Timestamp] ) , ALLEXCEPT ( 'Table' ,'Table'[SO] ) )
VAR _Max = CALCULATE ( MAX ('Table'[Timestamp] ) , ALLEXCEPT ( 'Table' ,'Table'[SO] ) )
VAR _TotTime = _Max - _Min
RETURN
_TotTimeOutput Mins =
VAR _1 = INT ( HOUR ( [Duration] ) ) * 60
VAR _2 = INT ( MINUTE( [Duration] ) )
VAR _3 = SECOND ( [Duration] ) / 60
VAR _Secs = _1 + _2 + _3
RETURN
_SecsOutput Secs =
VAR _1 = [Output Mins] * 60
RETURN
_1Hope this helps!
Theo 🙂
Hi learner03 refer to attached. The output is as per below.
Let me know if you want me to run you through the above.
The Total Scans measure counts each record by the SO group.
Total Scans = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' ,'Table'[SO] ) )
The division is both by Scan Rate Mins and Scan Rate Secs and it's just the Output Mins or Output Secs divided by the Total Scans.
All the best!
Theo 🙂
Hi learner03 ,
Please simply try:
Total Time (minutes) = DATEDIFF(MIN('Table'[Timestamp]),MAX('Table'[Timestamp]),MINUTE)Total Scans = SUM('Table'[Total Scan])Scan rate = DIVIDE([Total Scans],[Total Time (minutes)])Shown in Matrix visual:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous Yes I used, this works only if the data is of one day. But, it there are multiple days and I want to aggrregate time day-wise, then it takes minimum time and max time of that day, where as I need min max time based on wash sales order of that day (example- if there are 5 SO in one day then, mini max of SO1+Min Max of SO2....so on) and then calculate scan rate.
Hi learner03 ,
Could you tell me if your problem has been solved? If it is, kindly Accept a helpful post as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Best Regards,
Eyelyn Qin
