Forum Discussion
Find next highest value
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.
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.
4 Replies
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.
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"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
v-zhangti is there also a way to do this with a measure ?