Pivot table calculation based on distinct users

[bark01]

Hi,

My source data has: userid - > Departments -> week ending dates -> jobs raised

I want to show the average number of jobs raised per department in each week. I don't know how to get a count of unique of user ID other than to have user ID as a row in my pivot.

I need to know how many distinct user IDs there are per department / week , then divide that by the total jobs raised per department / week. I'm struggling with the first part of that equation.

 

[RoryA]

Which version of Excel are you using?

 

[bark01]

Which version of Excel are you using?

365 - got part of the way there but creating the pivot table as a data model and using distinct count, so my table had date on teh rows and department in columns and the data part had separate columns for unique users and total jobs.

I couldn't figure out how to get it to calculate in the pivot table so did the math off to the side.

 

[RoryA]

You'd need to create measures for the distinct count and the total jobs, then another one that simply divides the first two.