3 Replies Latest reply: Dec 17, 2014 5:20 AM by Gysbert Wassenaar RSS

    Partial Sums in Pivot Tables

      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.