0 Replies Latest reply on Mar 13, 2013 2:25 PM by dennis.schwartz

    How to add a Percentage column after Count column on the same row in pivot table

    dennis.schwartz

      Hello,

       

      I have a pivot table report that shows a member count by State.  It can drill down into Category to see the count by category.  It is broke down by week.

       

      Week Reported

      State

      Categories

      Week 1

      Week 2

      Week 3

      AR Membership

      New

      45

      28

      101

      Improved

      89

      78

      242

      No Change

      979

      23

      87

      More Accurate

      787

      23

      645

      Less Accurate

      2

      234

      234

      Exclude

      748

      748

      748

      Dropped

      235

      235

      235

      Filtered

      623

      623

      623

      Sub Total

      3374

      1886

      2572

      AZ Membership

      New

      45

      28

      101

      Improved

      89

      78

      242

      No Change

      979

      23

      87

      More Accurate

      787

      23

      645

      Less Accurate

      2

      234

      234

      Exclude

      748

      748

      748

      Dropped

      235

      235

      235

      Filtered

      623

      623

      623

      Sub Total

      3374

      1886

      2572

      Grand Total

      6748

      3772

      5144

       

      I now want to add a column after each of the membership count columns to show a percentage difference compared to the previous week.  So that it looks like this.

       

      Week Reported

      State

      Categories

      Week 1

      Week 2

      Week 3

      Count

      %

      Count

      %

      Count

      %

      AR Membership

      New

      45

      0%

      28

      2300%

      1. 101.00

      6500%

      Improved

      89

      0%

      78

      3400%

      242

      32

      No Change

      979

      0%

      23

      2300%

      87

      2300%

      More Accurate

      787

      0%

      23

      23400%

      645

      97

      Less Accurate

      2

      0%

      234

      0%

      234

      3100%

      Exclude

      748

      0%

      748

      9700%

      748

      79

      Dropped

      235

      0%

      235

      7800%

      235

      3200%

      Filtered

      623

      0%

      623

      24200%

      623

      5600%

      Sub Total

      3374

       

      1886

       

      2572

       

      AZ Membership

      New

      45

      0%

      28

      12300%

      101

      9700%

      Improved

      89

      0%

      78

      7300%

      242

      2300%

      No Change

      979

      0%

      23

      9000%

      87

      4500%

      More Accurate

      787

      0%

      23

      8700%

      645

      14300%

      Less Accurate

      2

      0%

      234

      3500%

      234

      4500%

      Exclude

      748

      0%

      748

      900%

      748

      7800%

      Dropped

      235

      0%

      235

      8700%

      235

      9800%

      Filtered

      623

      0%

      623

      8900%

      623

      7800%

      Sub Total

      3374

       

      1886

       

      2572

       

      Grand Total

      6748

       

      3772

       

      5144

       

      I am good with the % calculations, just need to know if it is possible to add the columns to populate the percentage differences.  Let me know if this does not make sense and I will clarify.

       

      As an alternative, I am not sure whether or not it is possible to add a blank column into a pivot table to then add the % column.

       

      Or I could concatenate the 2 fields into 1 field, so that it displays both values next to each other in the same row, I am not sure if it is possible, but I am unclear as to the correct syntax for doing so.

       

      Thanks for the help!