Subtotals for multiple expressions in pivot table?

[#1]

Hi,

I have a pivot table for annual sales forecasts, showing the 12 months of the year as the horizontal dimension, and the expression "sum(ForecastAmt)". I've been asked to add a second expression, which is "Sum(ForecastAmt)*ForecastProbability".

My pivot table shows correct sums for "Annual Total" at the far right, and the Monthly Totals for the first expression are correct. However, the monthly totals for the new expression don't show up, nor do the annual totals add up for the second expression to a grand total. Any thoughts?

[#2 · Accepted solution]

Can you try sum(ForecastAmt*ForecastProbability). 

The whole expression needs to be aggregated. In this case its sum().

Thanks

AJ

[#3]

Thanks, AJ! Worked perfectly.

[Anonymous]

you might have to use agggregate function for the dimension where you want the total . For example if you want the total to be at month. then your expression would be sum(aggr(sum(ForecastAmt*ForecastProbability),Month)). If this does solve pleae post the QVW doc with some sample data.

[veidlburkhard]

Hi Kevin: try this expression for your 'ProbAmt';

Sum(Aggr(Sum(Amount) * Prob, [Opp Name])).

Look at the simple example below:

Hope this helps,

Regards

Burkhard