In order to do this we use a table calculation. A table calculation allows you to use the values that you see in the view which are already aggregated. We also need to make the first cell of the partition act differently. Because in the syntax it would be looking up a value that could not exist (a value before it).
IF FIRST() = 0 THEN SUM([Yield])
ELSE (LOOKUP(SUM([Yield]),-1) + SUM([Yield])) / 2
So the function above says if its the first cell in a partition (row in this case) then return the sum of yield if it is not, lets say it is the third it will return the sum of the second and third month divided by two. I then made sure that the table calculation was running 'table across'. I assume you wanted each program calculated separately.@
Quality_Calculation.twbx 51.7 KB
1 of 1 people found this helpful
This sounds more like a rounding problem. Your values have more decimals than you are acknowledging. in your calculation.
For example Yield has 9+ significant figures. Also 99.74 and 99.77 would if they were just those two values with no trailing decimals would round to 99.755 or 99.76 when you round properly. So the comparison is not on target either. In order to make this value what you expect it to be you will need to use the ROUND() function around each portion of the calculation (around the SUM([Yield) and around the lookup of the sum yield) so that the numbers fit your expected.
However using the calculations show above if my values do not fit yours you should be able to use table calculations namely the lookup function to solve your goals. My goal here is to teach people to fish instead of giving them fish so I would hope instead of solving every issue for each person to be able to give them the tools and know how to solve future issues on their own.