Calculation Involving Duplicates

[Anonymous]

Without deleting duplicates, how do I write a formula to calculate the total scrap percentage by date?

Scrap% = (Machine Scrap/(Machine Scrap + Good Quantity)) * 100

Production Day	Machine Scrap	Good Quantity
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	33103	734164
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589
06/01/2023 12:00:00 AM	29452	744589

[Anonymous]

Here is a better representation of the table that I am trying to use. This information is for one machine, but there are multiple machines.

I want to be able to filter the Scrap percentage based on: Machine, Work Order, Material, Batch, and/or Production Day.

Also, I want to determine the percentage of scrap that is dependent on the Rsn Code.

Machine	Work Order	Material	Batch	Crew	Production Day	Rsn Code	Machine Scrap	Scrap Qty	Good Qty
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx1	33103	11241	734164
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx2	33103	2129	734164
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx3	33103	3586	734164
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx4	33103	3507	734164
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx5	33103	1253	734164
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx1	29452	5127	744589
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx2	29452	1376	744589
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx3	29452	3397	744589
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx4	29452	6295	744589
xxxx	xxxx	xxxx	xxxx	xx	06/01/2023 12:00:00 AM	xxx5	29452	692	744589
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx1	31577	11671	819528
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx2	31577	698	819528
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx3	31577	4532	819528
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx4	31577	2442	819528
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx5	31577	156	819528
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx1	37337	5668	422891
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx2	37337	2775	422891
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx3	37337	3861	422891
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx4	37337	70	422891
xxxx	xxxx	xxxx	xxxx	xx	06/02/2023 12:00:00 AM	xxx5	37337	37	422891
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx1	22584	5435	814593
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx2	22584	2470	814593
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx3	22584	1751	814593
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx4	22584	5103	814593
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx5	22584	343	814593
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx1	27079	15299	981785
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx2	27079	1036	981785
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx3	27079	590	981785
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx4	27079	312	981785
xxxx	xxxx	xxxx	xxxx	xx	06/03/2023 12:00:00 AM	xxx5	27079	110	981785

[lbendlin]

Please show the expected outcome based on the latest sample data you provided.

[Anonymous]

I hope this explains what I am looking for. Keep in mind that I have over a million rows of data and they are all in seperate files.

Machine	Work Order	Material	Batch	Production Day	Rsn Code	Machine Scrap	Rsn Scrap Qty	Good Qty	Total Scrap	Rsn Code Scrap
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx1	33103	11241	734164	0.043144042	0.015080393
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx2	33103	2129	734164		0.002891512
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx3	33103	3586	734164		0.004860725
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx4	33103	3507	734164		0.004754152
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx5	33103	1253	734164		0.001703795
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx1	29452	5127	744589	0.038049664	0.00683859
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx2	29452	1376	744589		0.001844591
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx3	29452	3397	744589		0.004541529
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx4	29452	6295	744589		0.008383452
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	xxx5	29452	692	744589		0.000928509
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx1	31577	11671	819528	0.03710118	0.014041162
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx2	31577	698	819528		0.000850985
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx3	31577	4532	819528		0.0054996
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx4	31577	2442	819528		0.002970911
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx5	31577	156	819528		0.000190317
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx1	37337	5668	422891	0.08112718	0.013225717
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx2	37337	2775	422891		0.006519196
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx3	37337	3861	422891		0.009047409
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx4	37337	70	422891		0.0001655
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	xxx5	37337	37	422891		8.74853E-05
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx1	22584	5435	814593	0.026976374	0.006627822
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx2	22584	2470	814593		0.003023023
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx3	22584	1751	814593		0.002144929
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx4	22584	5103	814593		0.006225479
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx5	22584	343	814593		0.000420892
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx1	27079	15299	981785	0.026841081	0.015343742
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx2	27079	1036	981785		0.001054109
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx3	27079	590	981785		0.000600585
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx4	27079	312	981785		0.000317688
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	xxx5	27079	110	981785		0.000112028

[Anonymous]

Hi @Anonymous ,

I suggest you to create two calcualted columns.

Total Scrap = 
IF('Table'[Rsn Code] = "xxx1",DIVIDE('Table'[Machine Scrap],'Table'[Machine Scrap] + 'Table'[Good Qty]),BLANK())

Rsn Code Scrap = DIVIDE('Table'[Rsn Scrap Qty],'Table'[Rsn Scrap Qty] + 'Table'[Good Qty])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Two issues with this, the first issue is that I have hundreds of different Rsn Codes, and the second issue is that if I want the total percent for Rsn Code xxx1 it will take an average of percentages and I do not want that.

I want to be able to get total percent for any machine and any rsn code. The total percent per machine will be Scrap% = Machine Scrap / (Machine Scrap + Good Qty) and the rsn scrap will be RsnScrap% = Rsn Scrap Qty / (Rsn Scrap Qty + Good Qty). I need to make it so that I can filter the total scrap percent by production day and filter rsn code scrap by rsn code.

[lbendlin]

The Machine and Batch columns are required to make this work.

[Anonymous]

It's my fault I wasn't clear with what I was trying to ask. What I want to do is use the first instances of the scrap and the good, but I think that I will have to create a new column to flag first instances.

[lbendlin]

Not necessarily. If you do a Table.Distinct in Power Query across only one column it will result in the "first"  column being grabbed and everything else being dropped.

If you want to do this in DAX you can abuse TOPN(1, ) in a similar way but it will be even less deterministic.

Please show the expected outcome based on the sample data you provided.

[lbendlin]