Issue with column sums in straight table

[mhassinger]

Alright, I have a table that looked like this.

           Sales Forecast | Actuals - Forecast | % Variance

           --------------------------------------------------------------------

Totals:    3,946.25        |   15,063.77            |    377.99%

                        0         |   4,1033.33            |

                        0         |     3,663.84            |

             3,946.25         |          49.97            |         1.26%

And so on. I've ommitted a few rows - all with 0 for forecast and thusly a large number in the "Actuals - Forecast" column. Unfortunately, it appears the forecast data doesn't persist after actuals are recorded for a month, and so this was causing an issue for the user, who was looking at that 377.99% number and going "that isn't right."

The user asked me to replace the 0 values in the forecast with the actuals, so that the totals would appear correctly. I used the following expression for the "Sales Forecast" column:

=if(SUM(Forecast) = 0, SUM(Sales), SUM(Forecast))

This works to give me a chart like this:

              Sales Forecast | Actuals - Forecast | % Variance

              --------------------------------------------------------------------

Totals:       3,946.25        |   15,063.77            |    377.99%

               4,1033.33        |               0             |        0.00%

                 3,663.84        |               0             |        0.00%

                 3,946.25        |          49.97            |        1.26%

So what's happening is that every number OTHER than the totals is now correct. If I change the Total Mode from Expression to Sum of Rows for the 1st and 2nd columns, those totals appear correctly, but it doesn't do anything to get that 377.99% to correctly display 1.26% as the total.

I'm at a loss as to why Expression Total for the first column is still insisting the total is 3,946.25 when the values underneath don't add up to it. Is there something wrong with my expression? I think if I could get the first column to work without doing Sum of Rows I'd be able to fix the others.

[mhassinger]

Ok, interesting - all the formatting got messed up. Hopefully it's still decipherable while I work on cleaning it up.

[johnw]

For the expression total, sum(Forecast) isn't 0, so it's doing a simple sum(Forecast) for the total, and thus is missing all of your actual sales data.

I'm not sure what's going wrong with your %, and for that matter I'm not even sure what your expression IS, but using this as your forecast expression might fix the %:

sum(aggr(if(sum(Forecast),sum(Forecast),sum(Sales)),Month))

I'm guessing here the same as you - if you don't have to use sum of rows, it may fix everything.  The above calculates the sum of rows directly in the expression, so you should be able to use expression total, and then I'm guessing it will plug properly into your % expression.