weighted average based on amounts produced in next steps

[Tommyvhod]

Hi guys.

I would like to make a DAX formula which seems to be more complicated as I hoped.

i have a product going trough different working stations, some are in progress and some are stopped as well.

I wanted to make an average production time for 1 product based on max quantities in working stations later in the process.

Product	Working station ID	Produced amounts	Times	Average	Weighted average
Prod1	20	165	5,45	0,033	0,0352
Prod1	30	128	91,96	0,718	0,5933
Prod1	35	125	0,33	0,003	0,0021
Prod1	60	92	62,13	0,675	0,4008
Prod1	80	82	0,01	0,000	0,0001
Prod1	110	65	44,77	0,689	0,2888
Prod1	123	57	0,30	0,005	0,0019
Prod1	150	169	77,16	0,457	0,4566

E.g : On working station 110 only 65 parts went trough but the overall produced amount is 169. So I want to divide all working stations operation times with the highest number after them. ( maybe from 10 product only 1 is reworked - i would like to divide that ones rework time with 10 to have an average additional time for rework for all the parts produced )

My excel formula is:

=IFERROR(G3/IF(D3>MAX(D4:$D$109);D3;IF(MAX(D4:$D$109)>$D$9;$D$9;MAX(D4:$D$109)));0)

Thank You 😊

[Jimmy801 · Accepted solution]

Hello @Tommyvhod 

i tried to work out a solution. I apply the logic as you described... divided times-column with the max-value found on the amounts from the current row till the end. However... i was not able to understand you figures.. meaning for row 110 times was devided by 158 and this amount a can't find nowhere. Please check out my solution and give it a try.

let
	Source = #table
	(
		{"Product","Working station ID","Produced amounts","Times","Average"},
		{
			{"Prod1","20","165","5,45","0,033"},	{"Prod1","30","128","91,96","0,718"},	{"Prod1","35","125","0,33","0,003"},	{"Prod1","60","92","62,13","0,675"},	
			{"Prod1","80","82","0,01","0,000"},	{"Prod1","110","65","44,77","0,689"},	{"Prod1","123","57","0,30","0,005"},	{"Prod1","150","169","77,16","0,457"},	
			{"Prod2","20","165","5,45","0,033"},	{"Prod2","30","128","91,96","0,718"},	{"Prod2","35","125","0,33","0,003"},	{"Prod2","60","92","62,13","0,675"},	
			{"Prod2","80","82","0,01","0,000"},	{"Prod2","110","500","44,77","0,689"},	{"Prod2","123","57","0,30","0,005"},	{"Prod2","150","169","77,16","0,457"}

					}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Average", type number}, {"Times", type number}, {"Produced amounts", type number}, {"Working station ID", type number}}),
    Group = Table.Group(ChangeType, {"Product"}, {{"AllRows", each _, type table [Product=text, Working station ID=number, Produced amounts=number, Times=number, Average=number]}}),
	WeightAverage= Table.TransformColumns
	(
		Group,
		{

			{
				"AllRows",
				(tableint)=>
				let 
					AddIndex = Table.AddIndexColumn(tableint,"Index", 1),
					AddWeight = Table.AddColumn(AddIndex,"WeightAverage", each [Times]/List.Max(Table.SelectRows(AddIndex, (sel)=> sel[Index]>= [Index])[Produced amounts]), Decimal.Type)
				in 
					AddWeight,
				type table
			}
		}
	),
    RemoveOther = Table.SelectColumns(WeightAverage,{"AllRows"}),
    Expand = Table.ExpandTableColumn(RemoveOther, "AllRows", {"Product", "Working station ID", "Produced amounts", "Times", "Average", "WeightAverage"}, {"Product", "Working station ID", "Produced amounts", "Times", "Average", "WeightAverage"})
in
    Expand

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Tommyvhod]

@Jimmy801 

Thank you for your response. The example I provided is only part of the data I am working with. I have 150 working stations and almost allways different. Not all product goes trough on the all and on the  same working stations.

I wanted to do it like indexing the working station becouse they are ascending and use max somehow find the highest number later in the index and divide with that.... but yeah... its not that easy for me 🙂

[Jimmy801]

Hello @Tommyvhod 

that is clear that your data is only an example. However, it should work also with a huge dataset. So, give it a try and let us know the outcoming

Jimmy

[Tommyvhod]

For the first part, with green, you entered all the working stations and overall times manually? ( sorry I dont really know the m language ). That would be impossible for 500000 rows and the data is dinamical.

[Jimmy801]

Hello

exactly

this to let you check, if this solution fits for you.

So, as I already stated, just copy my code to the advanced editor, and check out the single steps and the final solution. For such an complex request however it's not possible to instruct it to handle it with the UI.

If the solution works, we can talk about how to apply it to your dataset.

Jimmy

[Tommyvhod]

Yes. I created a new table and added the source code. for this case it works.

But for first view there is a problem adding it to my data model.

All my IDs and working stations are separate lines. First I have to create a table with the SUM of amounts and sum of times for each products. that I would be able to use this formula.

And I would like to slice the data with slicers ( that is why i wanted a measure ) , but maybe I could filter the table?

[Jimmy801]

Hello @Tommyvhod 

my proposal doesn't contain a measure. You never stated this. The solution adds a new column with the desired value. You could add this transformation to your data source and then add a DAX-measure to do the calculation for the average in your data model

Jimmy

[Tommyvhod]

In this case, it should work with data model with all work stations and IDs in separate lines? or should I create a separate table for it?

If yes , how would it be possible to slice it for dates or something?

Sorry for the too much question.

[Jimmy801]

Hello @Tommyvhod 

you never stated about this. The solution exactly starts from a table format that you have posted in your first post

Jimmy

[Tommyvhod]

Thank you for your help. This is a solution what you provided. I will study it and try to implement it on my data set. If I will stuck I will add another topic.

Thank You

[Jimmy801]

Hello @Tommyvhod 

exactly, try to apply it. If you have any question on it, you can't contact me.

Jimmy