Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there a possibility I can find the average per month (30 days) of a count sum column in a pivot table? If I use the average as summing value I get number one only

What I get is this:

Month   Transfers  Average 
June    1          1
July    2          1
August  1          1

Whereas it should be something like

Month   Transfers  Average
June    1          0.333
July    2          0.667
August  1          0.333

Is there an easy way to do this?

share|improve this question
    
Welcome to Super User! You can use the code button for fixed formatting, I've fixed it for you. :) –  Tom Wijsman Mar 29 '11 at 12:41
1  
Can you explain what you mean by "count sum" column. Usually they are either count or sum, but not both. –  paulmorriss Mar 30 '11 at 15:48
add comment

2 Answers

I think you are looking for the 'calculated field' functionality. Go to the Pivot Table "Options" when you highlight the pivot table in the ribbon and click on "formulas"

enter image description here

Then the calculated field option. Which presents you with the following:

enter image description here

Name your field 'average' and then write the formula required. To reference a field already in the pivot you need to enclose the field name in '

So your formula would look like:

='Month'/30

Click OK to add it and you end up with a calculated field attached to the pivot table:

enter image description here

share|improve this answer
add comment

Yes, assuming you have MONTH in Row Labels, and a COUNT/SUM of TRANSFERS in Values.

  • Add an additional COUNT/SUM of TRANSFERS in Values, to make a duplicate.
  • Click on Value Field Settings for that new count
  • Go in the "Show Value As" Tab
  • Change Normal to % of Column

This will always show a grand total of 100%, or all your transfers (4).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.