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

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.

 

MachineWork OrderMaterialBatchCrewProduction Day Rsn CodeMachine ScrapScrap QtyGood Qty
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx13310311241734164
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx2331032129734164
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx3331033586734164
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx4331033507734164
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx5331031253734164
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx1294525127744589
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx2294521376744589
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx3294523397744589
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx4294526295744589
 xxxxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM xxx529452692744589
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx13157711671819528
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx231577698819528
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx3315774532819528
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx4315772442819528
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx531577156819528
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx1373375668422891
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx2373372775422891
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx3373373861422891
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx43733770422891
 xxxxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM xxx53733737422891
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx1225845435814593
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx2225842470814593
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx3225841751814593
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx4225845103814593
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx522584343814593
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx12707915299981785
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx2270791036981785
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx327079590981785
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx427079312981785
 xxxxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM xxx527079110981785

 

lbendlin's avatar
lbendlin
Super User
3 years ago


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

  • 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
    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

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

    • 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.