Cumulative Calculation

[#1]

Hi All,

Thanks in advance!  I have what seems to be a simple request:  Show cumulative Sum(Sales)/Sum(Quantity) over time.  The 'Cumulative' check box on the expression tab is not working for this and I'll show you why.  I have data like this

What I want is cumulative average sales for each month.  It would look like this

Month 1 C Avg Sales = 25/12 = 2.1

Month 2 C Avg Sales = (25+23) / (12+13) = 1.9

Month 3 C Avg Sales = (25+23+27) / (12+13+15) = 1.875

etc.

And for month 12 it would be 357 / 240  as the total would show.

Instead, what I get is

Month 1 Avg Sales = 25/12 = 2.1 (Good)

Month 2 Avg Sales = 23/13 = 1.76 (wrong)

Month 3 Avg Sales = 27/15 = 1.8 (wrong)

etc. 

When I check the cumulative check box on the expression tab it gives me the right individual Sales and Quantity numbers that I want to use for each month BUT when I do the calculation Avg Sales [ Sum(Sales)/Sum(Quantity) ] and check cumulative, I get the current calculated Avg Sales + Last month calculated Avg Sales + Last 2nd month calculated Avg Sales, etc.  In other words, it Calculates Avg Sales and then Accumulates where as what I want is to Accumulate each piece (Sales and Quantity) and then Calculate Avg Sales. 

Many thanks!

[tresesco · Accepted solution]

Try :

=RangeSum(Above(total sum(Sales),0,RowNo()))  / RangeSum(Above(total sum(Quantity),0,RowNo()))

[#3]

That is looking sweet so far!  Thanks tresesco!