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.



      LOB PremiumsEarned PremiumIncurred ClaimsRetention %ProfitProfit Retained
      ENGINEERING & CONSTRUCTION7454665745466581952236.3%49643441803044
      MARINE HULL509154509154029.6%394594116941
      PERSONAL ACCIDENT289628960100.0%22452245
      Total 26500687 26500687 12682428 47.2% 7904837 3730390


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


      Thanks in advance.