Please find example data set, I shall try my best to explain!
In my current data (red on excel) I have one key field with different pieces and locations associated to it. If users have done their jobs properly, when they split pieces (so pieces break up and move locations) they should pro rata the weight and cbm down (like rows 2 & 3). Unfortunately this is not always the case (see rows 4 & 5) - the record pulls in the same weight and cbm as this is the only record present (for both lines)
The green on the example is what I am trying to get achieve. I need to Sum the pieces (when the weight & CBM are the same), divide the total weight and cbm by the total number of pieces, to get weight and cbm per piece, and then multiply by the individual line number of pieces to get my pro rata'd figure.
I tried - ((Weight)/ Sum(TOTAL([Pieces])) * [Pieces]) but this calculates across the whole key field (rows 2 - 5).
Matt, have a look at the Design Blog link below, I think it may be along the lines of what you are looking for here, but I am not certain. My post will kick things back up as well, so someone else may see things again and offer up something else too.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.