2 Replies Latest reply: Jul 25, 2011 5:53 PM by Bikash Debnath RSS

    Pivot table partial sum issue

      Hello Everyone,

      I have the following pivot table as shown in the attached screenshot.

      The problem is i am not getting a correct value for "% of Total Value" and "% of Opportunity".

      The requirement is: % of Total Value should calculate the percentage contribution of the bucket, stage and cyclic group.

       

      If you can kindly have a look into the screenshot,

      what I want in % of Total Value is a ratio?

      For example for the first line it should be

      2,362,381,545/2,362,381,545 = 100%

      Second line should be 316,643,697/316,643,697= 100%

      and the third line should be 609,040/609,040 =100%

      but the 9th line should be 119,925,268/161,821,219= 74.1%

      and the 15th line should be 11,160,830/11,285,790= 98.89% and so on.

       

      And I have no clue how to do it.

      I need you all experts' help.

       

      Thanks,

        • Pivot table partial sum issue
          Erich Shiino

          The actual expression you should use will depend on the field (and group) names you are using.

          But if the Total Value expression is: sum (Value)

          The % expression will be:

          =Sum(Value)/

          Sum(total <GroupName, Stage, Bucket> Value)  //Where GroupName should be replaced by the name of the cyclic group on your chart.

           

          Hope this helps,

           

          Erich

            • Pivot table partial sum issue

              Hi Erich,

              Thanks for your answer. But it seems like the expression is not

              taking the cyclic groups' name. In return its gving me all zeros.

               

              This is what I wrote in the denominator of the expression.

              sum({$<

              FACT_TYPE={'Current'},

              FLAG_HEALTH_REPORT={'1'},

              REVENUE_RECOGNITION_FLAG ={'Yes'},

              IS_PART_ACTIVE={'1'},

              MODIFIED_DATE={"<=$(vMaxDate)"}

              >}

               

              total <[Group 1],STAGE, Bucket_Test>  [TOTAL_VALUE])

               

               

              Thanks,