Calculate Average Clients per month in PivotTable

[Peter A.]

Dear Excel Experts,

I like to get your advice about how to calculate average clients per month in pivot table directly. Please refer to attached sample data.

In pivot table, I have the total count of clients and like to calculate average client per month serviced by the doctor and the expected results are marked in Yellow. I tried to directly create a calculated filed in pivot table to generate it but it does not work.

So any suggestions are greatly appreciated.

Best,
p

 

[Hui]

Peter

Firstly, Welcome to the Chandoo.org Forums

Add a new field to the data No_Clients
C2: =COUNTIFS($A$2:$A$70,A2,$D$2:$D$70,D2)
Copy down
The change the PT to Average No_Clients



See attached file:

 

[Peter A.]

Peter

Firstly, Welcome to the Chandoo.org Forums

Add a new field to the data No_Clients
C2: =COUNTIFS($A$2:$A$70,A2,$D$2:$D$70,D2)
Copy down
The change the PT to Average No_Clients

View attachment 35601

See attached file:

Thanks Hui. It works. I really appreciate your time and help.