Partial Sums in Pivot Tables

[adhudson]

Hi,

I have the following pivot table with 2 dimensions one of which is created using ValueList() function as given below.

=ValueList('Capping Volume', 'Top Retail Deals', 'Top Retail Deals Ordered', 'Top Retail Deals vs. CAP volume')

And my expression is as below

=pick(

   match(ValueList('Capping Volume',

   'Top Retail Deals',

   'Top Retail Deals Ordered',

   'Top Retail Deals vs. CAP volume'),

  'Capping Volume',

  'Top Retail Deals',

  'Top Retail Deals Ordered',

  'Top Retail Deals vs. CAP volume'),

   SUM(Capping),

   SUM({<Top_Retail = {'Y'}>} quantity),

   SUM({<Top_Retail = {'Y'}>} IF(NOT IsNull(Ordered), quantity)),

      SUM({<Top_Retail = {'Y'}>} quantity)/SUM(Capping)

     )

I want to see the Partial Sums (EMEA) for Region which I have done from presentation tab of pivot table. And the partial sum is working for first three rows. But in the 4th row, It does not sum up the values of the row instead it is dividing the value of partial sum of first two rows.

So my question is, how do I get the summation of the row values in the last row?

Thanks for the help.

Best Regards,

Andrew Hudson.

[Gysbert_Wassenaar]

Try aggregating that last expression over the Region dimension:

sum(aggr(SUM({<Top_Retail = {'Y'}>} quantity)/SUM(Capping),Region))

[adhudson]

I tried as per your suggestion and changed the expression as below

=pick(

   match(ValueList('Capping Volume',

   'Top Retail Deals',

   'Top Retail Deals Ordered',

   'Top Retail Deals vs. CAP volume'),

  'Capping Volume',

  'Top Retail Deals',

  'Top Retail Deals Ordered',

  'Top Retail Deals vs. CAP volume'),

   SUM(Capping),

   SUM({<Top_Retail = {'Y'}>} quantity),

   SUM({<Top_Retail = {'Y'}>} IF(NOT IsNull(Ordered), quantity)),

      SUM(Aggr(SUM({<Top_Retail = {'Y'}>} quantity)/SUM(Capping), Subs))

     )

But after that, the last row disappears.

Note: Subs is actual dimension name and I renamed it in the dimension tab.

Is there any other way to achieve it?

[Gysbert_Wassenaar]

Please post a document that demonstrates the problem.