Dividing by grand total of a column

[StandardSanders]

I am currently working with a powerpivot table and trying to create a measure or some means to accomplish the following.

I have 2 columns I am working with both are string values (one is a ticket number the other is an agent's name). Currently I have it set up with Column A is Count of Ticket ID and Column B is Distinct Count of Agent Name.

What I am trying to get should be fairly simple but is escaping me. I want to take the Count of Ticket ID and divide it but the Grand Total of the Distinct Count of Agent Name. Basically I need a column that will show me the Column A/Grand Total Column B for each agent.

The idea is that I can get an idea of how much work an agent is doing by hour/day/month for a given span of time. For example if 100 tickets come in at 10:00 AM on January 1 and there are 10 agents then each agent should be able to do 10 tickets. If they did 10 it'd be 1, 5 would be .5, etc.

Doing % of Parent Row doesn't work for me because it gives a percentage of the work done but does not take into account how many agents worked at that time. It'd simply show me that the agent who worked 10 tickets out of 100 did 10% of the work in that time period.

Let me know if I need to provide more information as this seems like it should be simple and I'm probably missing something basic here.

 

[scottsen]

For a variety of reasons, I avoid the "implicit measures", preferring to explictly create them.

TicketCount := COUNTA(TheTable[TicketId])
AgentCount := DISTINCTCOUNT(TheTable[AgentName])

THe only thing "magic" about a grand total cell is that it doesn't have a filter context, where typically each row is filtered by something. So, the typical pattern is:

=CALCULATE([SomeMeasure], ALL(TheTable[SomeColumn]))

where SomeColumn is whatever you have on rows of your pivot.

 

[StandardSanders]

Thanks Scott this worked great. The one thing that I need to figure out now is something that I forgot.

My data has a list of people not on my team, these folks should not be calculated into any of this but what I notice is using =CALCULATE([SomeMeasure], ALL(TheTable[SomeColumn])) I get 39 for the first month where if I do it with the filtered implicit measure the grand total is (correctly) 20.

This is because my List is filtered to only show agents on my team. I could manually extract those from the data table but I'd like to avoid that if at all possible.

 

[scottsen]

In the data, how are teams marked? Team Id? You can just add another filter to the CALCULATE...