Find next highest value

[Anonymous]

Hi there,

I'm having trouble to find the next highest value in a column. 
In one table i have the actual zone and number of kilo's in a row. I need a calculated column (or by measure) to calculate the rate for that specific row. The kilo's, zone and rate are in a separate table and that table looks like below. 

KG	Zone	Rate
1	1	6.29
1	2	8.50
1	3	9.00
2	1	6.75
2	2	9.25
2	3	10.00
3	1	8.50
3	2	10.00
5	3	11.00
7	1	9.50
8	2	11.50
9	3	13.00

Example: if KG = 2.5 & zone = 2 is should select the rate of 10.00. This means that it needs to look at the table and if the specific weight is not available it must look to the next highest value within that zone and select that rate. In this case it is rounding 2.5 to 3.

Example 2: if KG = 6 & zone = 3 is should select the rate of 13.00. 

I was able to make the calculated column myself but only when the weight was exactly matching the KG in the column. The issue i'm having is that i'm not able to write the code to look for the next highest value in the column based on another column. 

[Anonymous]

@v-zhangti is there also a way to do this with a measure ?

[Anonymous]

After the weekend, with a fresh mind, i tried it again and it seems i made a mistake the first time.
This is working.

Thank you @v-zhangti 

[Anonymous]

Hi @v-zhangti 
Thank you for your help.
I applied the provided code but i gives an error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist"

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Table1:

Table2:

Column = 
Var _nexthighKG=CALCULATE(MIN(Table1[KG]),FILTER(Table1,[KG]>=EARLIER(Table2[KG])&&[Zone]=EARLIER(Table2[Zone])))
Return
CALCULATE(MAX(Table1[Rate]),FILTER(Table1,[KG]=_nexthighKG&&[Zone]=EARLIER(Table2[Zone])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.