7 Replies Latest reply: Sep 30, 2013 9:26 AM by Yojas Samarth RSS

    Pivot Table Column Total & Subtotal Problem

      Hi guys,

       

      Having a pretty strange problem here. I have a pivot table with different expressions.

       

      The expressions are as simple as sum(value), sum(price) and so on.

       

      One of my expressions has a division, like sum(value * price)/sum(quantity).

       

      For every other expression in my pivot table, the totals and sub totals are correct, but for this expression, the totals and sub totals are wrong.

       

      Can someone help me with this please?

       

       

      Thanks

        • Pivot Table Column Total & Subtotal Problem
          Stefan Wühl

          Hi,

           

          the pivot table totals are always calculated as expression total (like putting the expression in a text box).

          (for more details see also the chapter "nested aggregations and related issues" - "sum of rows in pivot tables" in the Help file)

           

          To see what the exact problem in your case is, it would be best if you could post an example file demonstrating your issue.

           

          Reagrds,

          Stefan

            • Re: Pivot Table Column Total & Subtotal Problem

              Hi,

               

              I have attached a sample app to explain my problem.

               

              My subtotals and overall total in the pivot table are wrong, as the pivot table for some reason does not sum the total and sub total values correctly.

               

              Because I am using two groups, I tried using the aggr function, but couldn't get it to work properly.

               

              Please I need help to get my total and sub total values right.

               

               

              Thanks

                • Re: Pivot Table Column Total & Subtotal Problem
                  Stefan Wühl

                  Hmm,

                   

                  if I look at your pivot with dimensions Country and Product Type I see:

                   

                  CountryProduct TypeValueWeighted ValueCount
                  CanadaFurniture£140.295£731192730,7031
                  CanadaOffice£155.745£811192811,1719
                  CanadaTotal£296.040£771384770,9375
                  MexicoFurniture£93.427£487192486,599
                  MexicoOffice£106.639£555192555,4115
                  MexicoTotal£200.066£521384521,0052
                  U.S.A.Furniture£457.179£794576793,7129
                  U.S.A.Office£451.422£784576783,718
                  U.S.A.Total£908.600£7891152788,7155
                  Total £1.404.706£7321920731,6178

                   

                  (I 've added last two columns to show count and average value calculated in Excel.

                   

                  The Weighted Value seems ok to me, also in subtotals and total. For a given segment, the subtotal Wighted Value is of course not the average of the Weighted Values of the single rows.

                   

                  Could you give an example of what you expect to see?

                   

                  Regards,

                  Stefan

              • Re: Pivot Table Column Total & Subtotal Problem
                Yojas Samarth

                Hi Razor,

                 

                Even I faced such type of problem in my Pivot.... You can get perfect subtotal if you use  :

                =Sum(Aggr(.....Put your entire Expression here.....))

                If still you didnt get correct total then let me know...

                 

                Regards,

                Yojas