Thinking in terms of a weighted average value the only thing I can imagine doing is totalling the quantity and the values and then dividing the total value by the total quantity.
If you are producing a summary with mutliple lines for the same item, is there any way you can see to regroup the summary so that at some point all occurences of the same item description will meet up on the same line? My inital reaction would be to make the Item description a key field in the summary (or a version of it), assuming that the descriptions are indeed identical.
Failing that wouold it be possible to generate a pseudo code that links the spearated records together and then use that as a key field in the summary?
Or have I missed something?
I have created a summary that sums up these two figures by the item description, each item may appear on more than one detail line. What I need to calculate is the unit price for each item. How do I do this?
I have tried created a field for unit price but when I summarise this I can only sum or average it. Neither of these give the right answer. In effect I need a weighted average of the unit prices of these lines (OR total amount divided by total volume).
Any ideas how I can get a unit price for an item when the item will appear on more than one detail line with very different volumes in each line.
In V7 you need to do a measure calculation something like this:
So you would have to have your amount and qty fields defined as measures. You would not the unit price calculated field involved at all.
Just add amount as a measure again, after you have added it for the sum calculation type, double click on it, then on the calculation tab select other and edit the expression to what you desire.
In V6 you could use a Ratio aggregation to do the same thing.
Thanks for the response.
I can group the item fields together in the summary panel so that I see the total quantity and amount for that description. But what I can't seem to do is then divide one total by the other in the summary panel. Is this possible?