1 Reply Latest reply: Aug 16, 2016 9:04 AM by Marcus Sommer RSS

    Pivot Table with additional Columns

    kushal thakral

      Hi All

      I am actually looking for additional columns in Pivot table so that i can show overall Total for 12 Months , Average and Percentage difference like below

      Can any one has solution for this as i am aware you can not add additional columns, the other work around i am doing is having a separate straight table for 12 months total, average and percentage differences but i actually wanted to that in pivot table itself so if user wants to import he can import the entire 12 month data and total, avg and percentage diff

       

          

      JanFebMarAprMayJunJulAugSepOctNovDecJan
      TestTestTestTestTestTestTestTestTestTestTestTestTest12Month Total12Mth Average
      A2931374965655363563637894963052.50
      B7878762539577230366426133755346.08
      C5579607879739859422678209178365.25
      D8369775285878163645758752379165.92
      E4086681264839925894198295875262.67
      F7430964024226326667587719769758.08
      G3864588222994191119483606076563.75
      H6797195390905546247443457771359.42
      I9930617384334843449314319865254.33
      J2712395310419360572099408360750.58
      K7333483783398534165916861254845.67
      L36946227681009291773334112871759.75
      M4136922214728413319625497560950.75
      N4042297437945479497156732368156.75
      O6755804533555945636858109266355.25
      P5285402192208062819910473667356.08
      Q6925709874179810647390745775062.50
      R9034324316826579896315302857648.00
      S4125306341105479818543873563352.75
      T24712356303211261452761940450

      37.50

       

      Thanks

      Kushal Thakral

        • Re: Pivot Table with additional Columns
          Marcus Sommer

          The simplest way is to add a further horizontal dimension above your month-dimension (maybe Year) and then enabling partial sums for both horizontal dimensions and query them within your expression with:

           

          if(secondarydimensionality() = 0, AvgExp, SumExp)

           

          Otherwise you will need to create extra field-values for your month-dimension like sum and avg (maybe an additionally field only for these purpose) and query then which value your extended dimension had:

           

          if(Month = 'avg', AvgExp, if( Month = 'sum', TotalSumExp, SumExp))

           

          - Marcus