Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I want to calulate performance for machines. I got this data:
Time | Machine | Quantity | WorkTime | Planned1PcTime |
2020-04-28 16:16:08 | MACHINE-1 | 0 | 0 | 48 |
2020-04-28 16:16:08 | MACHINE-2 | 0 | 0 | 60 |
2020-04-28 16:16:08 | MACHINE-3 | 0 | 0 | 20 |
2020-04-28 16:16:08 | MACHINE-4 | 0 | 0 | 90 |
2020-04-28 16:16:39 | MACHINE-1 | 0 | 0.4 | 48 |
2020-04-28 16:16:39 | MACHINE-2 | 0 | 2.6 | 60 |
2020-04-28 16:16:39 | MACHINE-3 | 0 | 2 | 20 |
2020-04-28 16:16:39 | MACHINE-4 | 0 | 1.2 | 90 |
2020-04-28 16:17:09 | MACHINE-1 | 0 | 30.2 | 48 |
2020-04-28 16:17:09 | MACHINE-2 | 0 | 30.2 | 60 |
2020-04-28 16:17:09 | MACHINE-3 | 0 | 20 | 20 |
2020-04-28 16:17:09 | MACHINE-4 | 0 | 30.2 | 90 |
2020-04-28 16:17:40 | MACHINE-1 | 0 | 30.2 | 48 |
2020-04-28 16:17:40 | MACHINE-2 | 0 | 30.2 | 60 |
2020-04-28 16:17:40 | MACHINE-3 | 1 | 16.2 | 20 |
2020-04-28 16:17:40 | MACHINE-4 | 0 | 30.2 | 90 |
2020-04-28 16:18:11 | MACHINE-1 | 0 | 30.2 | 48 |
2020-04-28 16:18:11 | MACHINE-2 | 1 | 24 | 60 |
2020-04-28 16:18:11 | MACHINE-3 | 0 | 5.8 | 20 |
2020-04-28 16:18:11 | MACHINE-4 | 1 | 21.2 | 90 |
2020-04-28 16:18:41 | MACHINE-1 | 1 | 17.6 | 48 |
2020-04-28 16:18:41 | MACHINE-2 | 0 | 30.2 | 60 |
2020-04-28 16:18:41 | MACHINE-3 | 0 | 22 | 20 |
2020-04-28 16:18:41 | MACHINE-4 | 0 | 30.2 | 90 |
2020-04-28 16:19:12 | MACHINE-1 | 0 | 30.2 | 48 |
2020-04-28 16:19:12 | MACHINE-2 | 0 | 14.4 | 60 |
2020-04-28 16:19:12 | MACHINE-3 | 1 | 22 | 20 |
2020-04-28 16:19:12 | MACHINE-4 | 0 | 30.2 | 90 |
2020-04-28 16:19:43 | MACHINE-1 | 0 | 30.2 | 48 |
2020-04-28 16:19:43 | MACHINE-2 | 1 | 21 | 60 |
2020-04-28 16:19:43 | MACHINE-3 | 1 | 13.6 | 20 |
2020-04-28 16:19:43 | MACHINE-4 | 0 | 30.2 | 90 |
2020-04-28 16:20:13 | MACHINE-1 | 0 | 20.2 | 48 |
2020-04-28 16:20:13 | MACHINE-2 | 0 | 30.2 | 60 |
2020-04-28 16:20:13 | MACHINE-3 | 1 | 16.6 | 20 |
2020-04-28 16:20:13 | MACHINE-4 | 0 | 30.2 | 90 |
2020-04-28 16:20:44 | MACHINE-1 | 1 | 29.2 | 48 |
2020-04-28 16:20:44 | MACHINE-2 | 1 | 17.4 | 60 |
2020-04-28 16:20:44 | MACHINE-3 | 0 | 13.8 | 20 |
2020-04-28 16:20:44 | MACHINE-4 | 1 | 23.8 | 90 |
2020-04-28 16:21:15 | MACHINE-1 | 0 | 30.2 | 48 |
2020-04-28 16:21:15 | MACHINE-2 | 0 | 30.2 | 60 |
2020-04-28 16:21:15 | MACHINE-3 | 1 | 22 | 20 |
2020-04-28 16:21:15 | MACHINE-4 | 0 | 30.2 | 90 |
2020-04-28 16:21:45 | MACHINE-1 | 0 | 30.2 | 48 |
2020-04-28 16:21:45 | MACHINE-2 | 0 | 30.2 | 60 |
2020-04-28 16:21:45 | MACHINE-3 | 1 | 7.8 | 20 |
2020-04-28 16:21:45 | MACHINE-4 | 0 | 30.2 | 90 |
It's ok when filter is set to one of the machines. But when i want to see performance for all machines, it's wrong, because Planned1PcTime is different for every machine. It should count performance for each meachine and then count average for those machines performance - in this case: sum of average performance for every machine (seperately) devide by 4(machines). I want to shaw data in line chart in time bins (for this example 2 minutes).
I've tried many ways mentioned in other posts but nothing works correctly.
I'm sending data from devices every 15 secounds.
General formula for performance is:
PerformanceAVG = (SUM(Table[Quantity])*AVERAGE(Table[Planned1PcTime]))/SUM(Table[WorkTime])
Overall:
Machine-1 Performance = 34,43%
Machine-2 Performance = 69,07%
Machine-3 Performance = 74,17%
Machine-4 Performance = 62,54%
Performance for all machines should be 60,05% NOT 71,64%.
I'll be gratefull for any help 🙂
Solved! Go to Solution.
Hi @pak
Try this
Measure =
AVERAGEX(
VALUES( 'Table'[Machine] ),
CALCULATE(
DIVIDE(
SUMX(
'Table',
'Table'[Quantity] * 'Table'[Planned1PcTime]
),
SUM( 'Table'[WorkTime] )
)
)
)
Hi @pak
Try this
Measure =
AVERAGEX(
VALUES( 'Table'[Machine] ),
CALCULATE(
DIVIDE(
SUMX(
'Table',
'Table'[Quantity] * 'Table'[Planned1PcTime]
),
SUM( 'Table'[WorkTime] )
)
)
)
Works great. Thanks!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |