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 DayMachine ScrapGood Quantity
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM33103734164
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
06/01/2023 12:00:00 AM29452744589
calculation
duplicate
Duplicate Values
Formula

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

      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.

  • 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