7 Replies Latest reply on Oct 2, 2013 9:22 PM by Alejandro Suarez

    Pivot with Union and RSUM - Column totals not ok

    Alejandro Suarez

      Hi All,

       

        I need to build a report like this:

       

      ProductRegionWeek1Week2Week3Week4Total
      Prod ARegion 112348
      Prod ARegion 211114
      Prod BRegion 122228
      Prod BRegion 3333312
      Total7891034
      Cumulative Total715243434

       

        I am able to achieve something similar using 3 "union all"

       

      ProductRegionWeek1Week2Week3Week4Total
      Prod ARegion 112348
      Prod ARegion 211114
      Prod BRegion 122228
      Prod BRegion 3333312
      Total7891034
      Cumulative Total715243480

       

        The problem, as far as I can tell, is that in the Pivot View, the default "aggregation rule" is setting to SUM all the rows, even thought each measure (in each union) has the proper set, the first two have SUM, and the last one (the RSUM one) has MAX.

       

        Is there a way to achieve this? Another option would be to have a column to Calculate the totals by Row, but I can't place it at the right of the reports in a Pivot, is this something possible?

       

        Any ideas?

       


      Thanks,
      Alejandro