Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |