PowerPivot - SUM of Aggregated Averages Not Correct

Question

I am attempting to show the avearage duration of the various parts of our hiring process via a pivot table from PowerPivot. So, for example, you will be able to see the average time a candidate is in the "interview" stage as part of the entire process. The fact table I'm working with AppDuration is broken down by candidate, by position, and by duration. Here is a sample of the data:

RequisitionID    CandidateID      Req-CandidateKey   HireStatus     Duration
555              111              555_111            App Review     3
555              111              555_111            Phone Screen   5
555              111              555_111            Interview      7
555              222              555_222            Phone Screen   10
666              333              666_333            App Review     12
666              333              666_333            Phone Screen   15
666              444              666_444            App Review     5
666              444              666_444            Phone Screen   4
666              444              666_444            Offer Extend   15

I also have a lookup table StatusMaster to group the various HireStatus values

HireStatus     StatusCategory
App Review     Initial
Phone Screen   Intermediate
Interview      Intermediate
Offer          Final

AppDuration and StatusMaster are joined via a relationship on HireStatus. I want to examine the average duration at the StatusCategory level.

My thought is that I first need a count of distinct instances of requisition-candidate combination, so I created measure

DistinctReqApplicants:=DISTINCTCOUNT(AppDuration[Req-CandidateKey])

Then sum the Duration field

DurationTotal:=SUM(AppDuration[Duration])

And then just divide the two

CandidateAvgDur:=DIVIDE([DurationTotal], [DistinctReqApplicants])

I then create a pivot table where drop CandidateAvgDuration in the values and the StatusCategory in the rows. But the problem is that the Grand Total does not equal the sum of the category averages

In my results the Grand Total should be 38.3 when you sum up the individual categories, but instead is 23.0. I don't have any other filters applied. I am baffled because this seems straight-forward based on examples I've looked up and posts I've referenced on here. However I am a total noob when it comes to PowerPivot so I'm sure my lack of experience is at work here. Any help or suggestion is appreciated.

Answer 1

Please try this:

CandidateAvgDur:=SUMX(
 VALUES(AppDuration[HireStatus]),
 DIVIDE([DurationTotal], [DistinctReqApplicants])
)

Is it possible a requisition-candidate can have more than one row per HireStatus. If not then change the DistinctReqApplicants measure to just a COUNTROWS(AppDuration) to improve performance.