7 Replies Latest reply: Jun 9, 2014 2:19 AM by Hatim Maskawala RSS

    Pivot Table Aggregation Help

      I am creating the following Pivot Table. The data contains multiple policies whose Premiums, Earned Premiums, etc are being calculated by normal Sum formula in the pivot. If you look at the last column Profit Retained the sub-total should be a sum of the values above. Instead its is calculating it based the sub-total row.

       

      Retention % = Sum (Retained Premiums) / Sum (Premiums)

      Profit = Sum (Earned Premium) - Sum (Incurred Claims)

      Profit Retained = [Profit] * [Retention %]

       

      For individual rows the profit retained is coming correctly. However, for the sub-total I do not want it to multiply but just take total of the already calculated values. Right now it is showing 3730390 (i.e. 47.2% * 7904837) however, if you total the individual rows it comes to -1922125.

       


      RA_Year

      LOB PremiumsEarned PremiumIncurred ClaimsRetention %ProfitProfit Retained
      2010CARGO106460106460066.4%8250954760
      CASUALTY74595374595335983299.4%200218198937
      ENGINEERING & CONSTRUCTION7454665745466581952236.3%49643441803044
      MARINE HULL509154509154029.6%394594116941
      MEDICAL6468720646872011042546100.0%-5708491-5708491
      MOTOR795587795587133776100.0%483232483232
      PERSONAL ACCIDENT289628960100.0%22452245
      PROPERTY104172521041725232675215.1%74861871127206
      Total 26500687 26500687 12682428 47.2% 7904837 3730390

       

      Can you please guide me as to what I am doing wrong here.

       

      Thanks in advance.