Average giving wrong results - Possible wrong relation?

[illio]

Hi there,

I seem to be getting incorrect averaging results, when trying to get a fixed average over a table (with AVERAGEX). I think it may be due to PowerPivot inferring an incorrect relationship, but I'm unsure.

First a little background: I'm currently working on a dashboard where I have cases, case handlers, departments, case subjects and full case history. This means that in FactCases I have several rows per CaseID, where each of these has a Revision (the number of the change) and a Status (Open/Closed + a few others that aren't important).
The relevant dimensions for the issue here are:

DimCaseHandler: HandlerKey, HandlerUID, Name, Login, StartDate, EndDate
DimDepartment: DepartmentKey, DepartmentID, Name, StartDate, EndDate

If we look at some dummy data from DimCaseHandler (ignoring login, startdate and enddate), we have:

1; Anders Andersen_()_1; Anders Andersen
2; Benny Bennysen_()_2; Benny Bennysen
3; SOMEDOMAIN\claimsteam_()_2; claims
4; Anders Andersen_()_2; Anders Andersen
5; Anders Andersen_()_3; Anders Andersen
6; Anders Andersen_()_4; Anders Andersen
7; Anders Andersen_()_5; Anders Andersen
8; Anders Andersen_()_6; Anders Andersen
9; Benny Bennysen_()_5; Benny Bennysen
10; SOMEDOMAIN\salesteam$_()_5; sales$

So different case handlers can have the same name, but they'll have a different HandlerUID and a different HandlerKey (the primary key). HandlerUID is constructed as [Name]_([Login])_DepartmentID .. In this case logins aren't used, so they're empty.

Now the problem is I'm trying to create a PivotTable with the following information:

Handler Name	Avg. completion time	KPI	# cases closed	KPI	# cases in total	KPI	Completion percentage	KPI
Carsten C.	37 days	green	10	red	210	green	4.7%	red

<tbody>
</tbody>

Where each KPI compares that CaseHandlers performance with the average performance for all CaseHandlers. So e.g. if Carsten C closes 10 cases in this given timeframe and the average for all casehandlers is 22, that'll be a red flag.

To do this I introduced the following measures in FactCases, in order of occurance in the above table:

Avg. completion time

AvgCaseCompletionPerCaseHandler:=ROUNDUP(CALCULATE(AVERAGE(FactCases[CompletionTime]); FILTER(ALLEXCEPT('FactCases'; FactCases[HandlerKey]); FactCases[Status] = "Closed")); 0)

KPI on Avg. completion time relative to:

AvgCaseCompletion:=ROUNDUP(CALCULATE(AVERAGE(FactCases[CompletionTime]); FILTER(ALL('FactCases'); FactCases[Status] = "Closed")); 0)

# cases closed

NumberOfClosedCases:=CALCULATE(DISTINCTCOUNT(FactCases[CaseID]);FILTER(FactCases; FactCases[Status]="Closed"))

KPI of # cases closed relative to:

AvgNumberOfClosedCases:=CALCULATE(AVERAGEX(DimCaseHandler; [NumberOfClosedCases]); ALL(DimCaseHandler))

# cases in total

NumberOfCases:=CALCULATE(DISTINCTCOUNT(FactCases[CaseID]))

KPI of # cases in total relative to:

AvgNumberOfCases:=CALCULATE(AVERAGEX(DimCaseHandler; [NumberOfCases]); ALL(DimCaseHandler))

Completion percentage

CompletionPercentage:=[NumberOfClosedCases]/[NumberOfCases]

KPI on Completion percentage relative to:

AvgCompletionPercentage:=CALCULATE(AVERAGEX(DimCaseHandler; [CompletionPercentage]); ALL(DimCaseHandler))

Now, showing you all the data in FactTable is probably a bit much, but the resulting PivotTable looks like this:

Name	Avg. completion time	KPI	# cases closed	KPI	# cases in total	KPI	Completion percentage	KPI
Anders Andersen	1	green	5	green	17	green	29,41%	red
Benny Bennysen	1	green			4	green
SOMEDOMAIN\salesteam	1	green			1	red
SOMEDOMAIN\claimsteam	1	green	1	red	22	green	4,55%	red

<tbody>
</tbody>

Note that Benny Bennysen has not closed any cases and neither has the salesteam. Yet for some reason an avg. completion time gets computed for them anyway?
Also note that the average number of closed cases is, supposedly (according to PowerPivot): 2 .. Which doesn't quite make sense to me, regardless of how I compute it. AvgNumberOfCases also seems off, at 4,4. And the AvgCompletionPercentage is supposedly 49% (0,49), which seems completely wrong, when the best performer is Anders Andersen at 29%.

So all in all, there's clearly some weirdness going on here. What I think it might be is that while our data separates people if they're from different departments (regardless of whether they have the same name), PowerPivot doesn't do that. It only shows 1x Anders Anderson, even though we have 6 of him (different HandlerUID and different HandlerKey). Now, this may be fine in a sense, if it just meant PowerPivot assumed its the same person each time and treated all of them as the same. However I don't think that's what it's doing, because the results seem inconsistent. Sometimes it assumes they're the same and sometimes it doesn't.

So my questions are as follows:

1) How do I fix this if I want PowerPivot to treat handlers with the same name, as if they were the same person?
2) How do I fix this, if I want PowerPivot to treat handlers separately by their HandlerUID (or HandlerKey) such that the PivotTable gets 10 entries instead of 4 and such that the slicer for DimCaseHandler shows 10 options (e.g. with the department in paranthesis), rather than 4?

Any pointers?