Working with Sums of Averages and Percentages

[matthewjbryant]

I have what seems like a simple problem, but I don't have the knowledge to achieve what I want.

My data is such that I have detail lines for scrap joined with summary lines for quantity complete. In a table I can create a percentage column for the detail, but because I'm summing and averaging in separate columns I can't get the total percentage right.

In the attached example all the numbers show correctly, but the total percentage should be 2.41, not 4. Can you impart your knowledge please?

[fvelascog72]

Hi,

It could be: avg(aggr(sum([Op Scrap])/(avg([Total Good])+sum([Op Scrap])),Order))

But the result is not 2,47... avg gives 2,34

[matthewjbryant]

I made a mistake, but I still calculate the percentage as being 2.41. To get this I am doing the following

( 37 / (1500 + 37) ) * 100  =  2.41

The aggr() seems to simply be averaging the percentages.

[matthewjbryant · Accepted solution]

Got it, thanks. You had the aggr in the wrong place. The solution is

sum([Op Scrap]) / ( sum(aggr(avg([Total Good]),Order)) + sum([Op Scrap]) )

[fvelascog72]