Reply
avatar user
Anonymous
Not applicable

Calculation Involving Duplicates

‎06-06-2023 05:11 AM

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
Message 1 of 10
716 Views
0
9 REPLIES 9
avatar user
Anonymous
Not applicable

‎06-08-2023 05:14 AM

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

 

Message 5 of 10
691 Views
0
lbendlin
Super User
Super User
In response to Anonymous

‎06-08-2023 06:10 AM


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

Message 6 of 10
683 Views
0
avatar user
Anonymous
Not applicable
In response to lbendlin

‎06-08-2023 10:13 AM

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
Message 7 of 10
674 Views
0
avatar user
Anonymous
Not applicable
In response to Anonymous

‎06-09-2023 01:22 AM

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.

vrzhoumsft_0-1686298931723.png

 

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.

Message 9 of 10
651 Views
0
avatar user
Anonymous
Not applicable
In response to Anonymous

‎06-09-2023 05:12 AM

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.

 

 

Message 10 of 10
646 Views
0
lbendlin
Super User
Super User
In response to Anonymous

‎06-08-2023 11:07 AM

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

Message 8 of 10
669 Views
0
avatar user
Anonymous
Not applicable

‎06-08-2023 04:58 AM

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.

Message 3 of 10
697 Views
0
lbendlin
Super User
Super User
In response to Anonymous

‎06-08-2023 05:02 AM

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.

Message 4 of 10
695 Views
0
lbendlin
Super User
Super User

‎06-07-2023 06:30 PM

lbendlin_0-1686187840706.png

 

Message 2 of 10
711 Views
0
avatar user

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Stop
View All
Top Solution Authors (Last Month)
View All
Top Kudoed Authors (Last Month)
View All