I need to create a calculated field in Pivot table based on Date (Year&Month) and total number of clients for that Date. Thus, when Jan2012 selected from Filter, then the calculated field (denominator) should only pick total number of clients for Jan2012. I have two sets of data one for claims and one for clients. How do I create a Combo Box (or other any other methods) to perform the filtering. Below are samples of two tables + Final Pivot Table:Claim Table (Raw Data)
Client Table (Raw Data)
Final Table (Pivot)
Filter: YrMnth(Combo from Claim & Client Tables)
Thank you
YrMnth | Paid |
201201 | $1200 |
201202 | $1600 |
201203 | $1500 |
201204 | $900 |
YrMnth | Count |
201201 | 10 |
201201 | 10 |
201202 | 20 |
201203 | 15 |
201203 | 15 |
201204 | 10 |
201204 | 20 |
Final Table (Pivot)
Filter: YrMnth(Combo from Claim & Client Tables)
Yrmnth | Paid | Paidd/Client |
201201 | 1200 | 1200/20=60 |
201202 | 1600 | 1600/20=80 |
201203 | 1500 | 1500/30=50 |
201204 | 900 | 900/30=30 |
Thank you