2 Replies Latest reply: Mar 14, 2014 4:34 PM by Massimo Grossi RSS

    Pivot Table - Incorrect Partial Sum


      Hi,

       

      I was using Pivot table to do some calculations. But it does return correct partical sum for me. Please refer to below table. In the "Total" row, it gives me a number which is not equal to the real sum of "Total Cost" column.

       

      In the "Total Cost" column, my code is like: sum(Item Cost) * count(Item)  and I tried sum( total <Item> Item Cost) but didn't work. Can anyone help me on it? Thanks!

       

      AREAItemItem Cost Total Cost = Item Cost *UnitUnit
      WestA$13,581$13,5811
      WestB$27,303$27,3031
      WestC$113,845$113,8451
      WestD$13,480$13,4801
      WestE$10,000$20,0002
      WestF$35,625$249,3757
      WestG$5,864$5,8641
      WestH$207,494$207,4941
      WestI$5,707$11,4142
      WestJ$80,737$80,7371
      WestK$6,119$6,1191
      WestL$4,874$24,3705
      WestM$27,035$27,0351
      WestN$8,000$32,0004
      WestO$42,708$42,7081
      WestP$10,000,000$1,120,000,000112
      Total $1,508,668,766142
        • Re: Pivot Table - Incorrect Partial Sum
          Jeremiah Kurpat

          This is because it is not summing the rows, but calculating total Item Cost, then multiplying by the total Count. You want the sum of rows, in this you need different formula for total. You'll need aggr and dimensionality like:

           

          if(dimensionality() =1, sum([Item Cost]) * sum(Unit), sum(aggr(sum([Item Cost]) * sum(Unit), Item)))

           

          This is one that works in my example. For yours, maybe:

           

          if(dimensionality() =1, sum(Item Cost) * count(Item), sum(aggr(sum(Item Cost) * count(Item), Item)))

           

          Please find attached for example.

           

          Hope this helps!

           

          EDIT: You should be able to get away with just:

           

          sum(aggr(sum(Item Cost) * count(Item), Item))

          • Re: Pivot Table - Incorrect Partial Sum
            Massimo Grossi

            sum([Item Cost]*Unit)