calculated fields in pivot tables

[Juiceandbran]

I have a table with wage data per employee with dollars, hours and average hourly rate. The pivot table gives me an average hourly rate, not a weighted average. How do I get this?

 

[GlennUK]

Explain what the weighting should be. Isn't your data per employee?

 

[Juiceandbran]

pivot table

The average should be the total of dollars paid divided by total hours worked, not the average of the hourly rates at an individual level

div section employee time dollars hours average rate
1 23 smith ordinary 1500 100 15.00
1 23 jones ordinary 8000 400 20.00
Total 9500 500 17.50
Weighted average 19.00

 

[GlennUK]

It works fine for me when I have a calculated field for rate. Is yours a calculated field, or not?

 

[Juiceandbran]

Pivot tables calculated fields

Hi Glenn,
That is my problem, I cannot create a calculated field. When I right click on a cell in the pivot table, I get to see the formula option, but there are no options to see under formula. I am sure I have done this before and been able to divide total dollars by total hours to get the weighted average, but not this time.

 

[GlennUK]

That sounds very strange:

... but there are no options to see under formula

Are there options there that are greyed-out? Or will it just not show any options?