4 Replies Latest reply: Nov 1, 2012 9:08 AM by Eric Schmid RSS

    Pivot not allowing Subtotals

      Hello,

       

      Please see the attached example. I am trying to subtotal the Opportunity values by each Supplier_Proposal. I have selected the Partial Sums check box and Expand all options as other discussions have suggested without success. Can total Opportunity be isolated by Supplier_Proposal in this Pivot or a Bar chart? A straight table allows summation by all Suppliers_Proposal, but this does not do me any good. Any suggestions are welcome.

       

      Thanks!

        • Re: Pivot not allowing Subtotals
          John Witherspoon

          Well, I'm not certain if you just want a straight sum, but if so, if you change your first two expressions to these, you can subtotal the rows:

           

          sum(aggr(Rate_Proposal*[Days Proposed],Supplier_Proposal,[Normalized Role]))
          sum(aggr(Avg(Rate)*[Days Proposed],Supplier_Proposal,[Normalized Role]))

           

          To explain, pivot tables reevaluate the expression for the subtotals rather than summing up the rows.  To get a sum of rows, the general form is this:

           

          sum(aggr(your expression, your dimensions))

            • Re: Pivot not allowing Subtotals

              Thanks John, the above got me part of the way there in that I can now calculate the subtotal of each supplier as shown in the updated example attached.

               

              My next issue is that the the subtotals do not recognize my IF modifier that zero's out negative values. The reason I need this function to work is that negative numbers do not present an 'Opportunity' and therefore should not be calculated against the subtotal/total Opportunity for that Supplier.

               

              For example, the Opportunity values for Supplier_Proposal 'T' are -8, 27, 18 which subtotal correctly to 37. If I zero out -8 with an IF statement, however, the total remains 37 instead of updating to the sum total of 0, 27, 18. The new value should then be 45, but it remains at 37.

               

              I performed the same IF on the BM expression to zero out that value for Supplier_Proposal 'T' to test and the same thing occurs where the sum of 0, 45, and 20 is incorrectly shown to be 77 rather than 65.

                • Re: Pivot not allowing Subtotals
                  Jonathan Dienst

                  Hi

                   

                  Try this for your 'Opportunity' expression:

                   

                  Sum(Aggr(

                  RangeMax(0,

                            sum(aggr(Rate_Proposal*[Days Proposed],Supplier_Proposal,[Normalized Role]))

                            -

                            sum(aggr(Avg(Rate)*[Days Proposed],Supplier_Proposal,[Normalized Role]))

                  ), Supplier_Proposal,[Normalized Role]))

                   

                   

                  I get 0, 27, 18 and subtotal 45 for proposal T

                   

                  Hope that helps

                  Jonathan