Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculation Involving Duplicates

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 Machin...
calculation
duplicate
Duplicate Values
Formula
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

MachineWork OrderMaterialBatchProduction DayRsn CodeMachine ScrapRsn Scrap QtyGood QtyTotal ScrapRsn Code Scrap
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx133103112417341640.043144042 0.015080393
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx2331032129734164 0.002891512
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx3331033586734164 0.004860725
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx4331033507734164 0.004754152
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx5331031253734164 0.001703795
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx12945251277445890.038049664 0.00683859
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx2294521376744589 0.001844591
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx3294523397744589 0.004541529
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx4294526295744589 0.008383452
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AMxxx529452692744589 0.000928509
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx131577116718195280.03710118 0.014041162
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx231577698819528 0.000850985
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx3315774532819528 0.0054996
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx4315772442819528 0.002970911
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx531577156819528 0.000190317
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx13733756684228910.08112718 0.013225717
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx2373372775422891 0.006519196
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx3373373861422891 0.009047409
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx43733770422891 0.0001655
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AMxxx53733737422891 8.74853E-05
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx12258454358145930.026976374 0.006627822
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx2225842470814593 0.003023023
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx3225841751814593 0.002144929
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx4225845103814593 0.006225479
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx522584343814593 0.000420892
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx127079152999817850.026841081 0.015343742
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx2270791036981785 0.001054109
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx327079590981785 0.000600585
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx427079312981785 0.000317688
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AMxxx527079110981785 0.000112028
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.