Average count by pivot subtotal

[jbfx]

Please see the attachments

I have a pivot table that counts the number of instances of each actor for each location and subtotals by location. I would also like to average the count of instances of each actor for each location. Summarizing by average does not work.

I think I may need a Power Pivot measure or calculated field.

Please help!


Source Data Below. Picture of Pivottable attached

Location	Actor
1​	1234​
1​	1234​
1​	1235​
1​	1235​
1​	1235​
1​	1236​
2​	1237​
2​	1237​
2​	1238​
2​	1239​
2​	1239​
2​	1239​
2​	1239​
3​	1240​
3​	1241​
3​	1242​
3​	1242​
3​	1242​
3​	1243​
3​	1243​
3​	1245​
1​	1234​
1​	1234​
1​	1235​
1​	1235​
1​	1235​
1​	1236​
2​	1237​
2​	1237​
2​	1238​
2​	1239​
2​	1239​
2​	1239​
2​	1239​
3​	1240​
3​	1241​
3​	1242​
3​	1242​
3​	1242​
3​	1243​
3​	1243​
3​	1245​

 

[jbfx]

Figured it out myself. I had to use a helper column and CountIFS to get me the correct count, then I removed duplicates, and used that data as the pivottable. Not elegant, but worked for my project.s