Calculate Performance by group (machine)

[pak]

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 🙂

[Mariusz · Accepted solution]

Hi @pak 

Try this 

Measure = 
AVERAGEX(
    VALUES( 'Table'[Machine] ),
    CALCULATE(
        DIVIDE( 
            SUMX( 
                'Table', 
                'Table'[Quantity] * 'Table'[Planned1PcTime] 
            ),
            SUM( 'Table'[WorkTime] )
        )
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pak]

Works great. Thanks!

[Mariusz]

Hi @pak 

Glad I could help!

Best Regards,
Mariusz

Please feel free to connect with me.
LinkedIn