Find the nearest and highest value by ID

[nok]

I have two tables that follow this structure:
Order

ID	Qty
111	1000
222	4500

Scale

ID	ScaleQty
111	1000
111	2000
111	5000
111	10000
222	3500
222	6000
222	9000

I'd like to create a new column in the Order table that, for each ID, checks the quantity ordered (in the Order[Qty] column) and finds the nearest and highest quantity in the Scale[ScaleQty] column. It can't be the largest Qty of them all (for example ID 111, it can't be 10000); so for ID 111 it needs to be the nearest and highest quantity to 1000, which in this case would be 2000.
The final result of this new column would be something like this:

ID	Qty	NewColumn
111	1000	2000
222	4500	6000

How can I do this?

[Ashish_Mathur · Accepted solution]

Hi,

This calcualated column formula in the Orders table will work

=CALCULATE(MIN(Scale[ScaleQty]),FILTER(Scale,Scale[ID]=EARLIER('Order'[ID])&&[ScaleQty]>EARLIER('Order'[Qty])))

Hope this helps.

[Ahmedx]

pls try