Divide results from one pivot table with results from a second pivot table.

[Doja]

Hello

I need to calculate the average calls per hour per call agent in a certain period of time.

I made 2 pivot tables: one with the number of calls per hour and one calculating the number of days each call agent has worked (I clicked on "add the data to the Data Model" so I was able to calculate the unique number of days worked).

How can I divide the result of the first pivot table by the second?

Or is there a different way to approach this?


Best regards, DOJA

[AliGW]

Welcome to the forum.

How to attach your workbook: instructions at the top of the page.

[MarvinP]

Hi doja,

I've made an example of your data. I think you can do this with a single pivot table. You need to group your times (I've used days instead of time in my example) for your calls. See the attached for my idea of what you want.

Call Center Agent call Average per grouped time.xlsx

[tom.satterthwaite]

I have the same question but not able to merge into one table. I have two data sources, one being number of visits to a site during a month, and the other being the user data.

My analysis is dependent on being able to divide a monthly figure by the total number of users, and I want to use a pivot to be able to do this easily.

Example sheet is attached. An example of the key metrics I'm after are would be:

1. the number of active users of a type in a month - (UK users in Month 2 would be 33%)
2. the average number of visits across all users of a type in a month - (AUS visits per user in Month 2 = 22/2 = 11)

At the moment we are doing a horrible amount of copy and paste with a high chance of errors creeping in.

I think the answer is PowerPivot, but I've tried following various posts and have reached a dead end when I couldn't build relationships due to the duplicate values inevitable in the visit data.

Any help or observations of other ways of approaching this would be welcomed!


Because it is so similar i thought it better to add to the thread, rather than create a new post - apologies if that was not the correct etiquette!
thanks
Tom

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

As you are new, I shall start a new thread for you: https://www.excelforum.com/excel-cha...out-merge.html

[tom.satterthwaite]

Superstar thanks Ali - makes sense. Sorry for the hijack..