add a count column to a pivot table

[whisperwind]

Hi,
i am trying to make something conceptually simple that is driving me crazy ...
I am using Office 2013.

let's say i have the following table:

          A          B        C
1     seller       date       $
2     seller1     4-Jan      10
3     seller2     12-Feb     20
4     seller3     14-Mar     30
5     seller1     6-Mar      40
6     seller2     22-Feb     50
7     seller3     15-Jan     60

That will generate the following pivot (excuse me for the graphic) using "sellers" as rows, "date" as columns" and "sum of $" as values where "date" will then be grouped per months:

           Jan      Feb    Mar
seller1    $10             $40
seller2             $70
seller3    $60             $30

What I want to add is a column with the count of the months in which each seller has sold something, that in this case would be:

seller1 : 2
seller2 : 1
seller3 : 2

The goal would be to add it inside the pivot to be able to sort etc. and to avoid to add more data to the main table since it is already huge and adding calculation there would slow it down a lot.

I hope it is clear enough and thank you in advance for your help.

 

[Nebu]

Hi:

I am not sure whether you can achieve this through power pivot, In normal pivot it is not possible to achieve what you have asked through a calculated field . You will have to use a helper column to get the results. Find the attached.

Thanks