Min of AVG in Expression

[reporting_neu]

Hello together,

I have the following data:

Supplier	Part	ScaleQuantity	OrderDate	OrderQuantity
78268	98763	1	01.01.2024	328
78268	98763	50	01.01.2024	328
78268	98763	100	01.01.2024	328
78268	98763	500	01.01.2024	328
78268	98763	1	23.04.2024	409
78268	98763	50	23.04.2024	409
78268	98763	100	23.04.2024	409
78268	98763	500	23.04.2024	409
78268	98763	1	16.08.2024	73
78268	98763	50	16.08.2024	73
78268	98763	100	16.08.2024	73
78268	98763	500	16.08.2024	73

OrderQuantity is repeated in the table for each ScaleQuantity.

Target:
I have to find out which scale quantity matches the order quantity and by what percentage the order quantity differs from the scale quantity.

Procedure:
Step 1:
ScaleQuantity - Average of OrderQuantity
Like: ScaleQuality - AVG(OrderQuantity)

Step 2:
Take anything >0
Like: If(ScaleQuality - AVG(OrderQuantity) >0, ScaleQuality - AVG(OrderQuantity), Null())

Step 3:
Take the lowest value (min)
Like: Min(If(ScaleQuality - AVG(OrderQuantity) >0, ScaleQuality - AVG(OrderQuantity), Null()))

Because I have to select a time period afterwards, I unfortunately cannot create this in the script and have to write it in the expression.

Result:

Supplier	Part	ScaleQuantity	OrderDate	OrderQuantity
78268	98763	500	01.01.2024	328
78268	98763	500	23.04.2024	409
78268	98763	100	16.08.2024	73

Can you help me how I can implement this?

[marcus_sommer]

The data are looking a bit odd and/or the example/description isn't really complete. Nevertheless you may use something like this as starting point:

min(aggr(if(ScaleQuality >= OrderQuantity, avg(OrderQuantity)), Supplier, Part))

by optionally adjusting the aggr-dimensions and/or the if-condition and/or applying any TOTAL to get it to work like expected within the real requirement.

Beside this I suggest to re-think the data-model because the repeating scale-quantity may the result of a not suitable join. At least the comparing which record has a bigger scale to order quantity could be easily done within the script.