Flattened Power Pivot Table

[Mavericks334]

Hi,

I have Two tables Fact Tran 1 and Fact Tran 2

Fact tran 1 has the headcount by department and Fact tran 2 has the attrition by department.

When i try to create a flattened pivot table when the common information from both the tables are the dates and the department id's i am trying to get the total headcount by department and attrition by department which i could use to create a table and do a bit of datamining on it.

When i use the values from both the tables, i get a either the total headcount or the attrition numbers incorrectly

below is the information that i get.
[TABLE="width: 243"]
<COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><TBODY>[TR]
[TD="width: 68"]Date[/TD]
[TD="width: 121"]Heading2_Code[/TD]
[TD="width: 74"]Headcount[/TD]
[TD="width: 60"]Attrition[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9/1/2008[/TD]
[TD="bgcolor: transparent"]3310ED[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]786[/TD]
[/TR]
[TR]
[TD]9/1/2008[/TD]
[TD]3310FL[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]786[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9/1/2008[/TD]
[TD="bgcolor: transparent"]3310HD[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]786[/TD]
[/TR]
[TR]
[TD]9/1/2008[/TD]
[TD]3310PZ[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]786[/TD]
[/TR]
</TBODY>[/TABLE]


There is a relationship between both these tables. however i assume it is because of the relationship i am not getting the exact values.

REgards,
Ren

 

[Jacob Barnett]

Ren,

I suspect your relationship is the wrong way round!

I am guessing that you are talking about two tables that have unique values in the 'Heading2_Code' column and when you've created the relationship you've done it the wrong way round as PowerPivot only intervenes when its a clear many-to-one situation. If you are dragging in the 'Heading2_Code' from the headcount table then the attrition table needs to be on the 'top half' of the relationship.

I created a 2 second demo model here to prove the point: https://docs.google.com/file/d/0Bz5yMU2oooW2Ty1uelI0WVRLOXc/edit?usp=sharing

My recommendation in such scenarios is a 3rd table which is just a unique list of departments (plus other relevant info about the department) that you use to relate to both the other tables (the 2 fact tables won't be relate-able one you get multiple instances of the department name).

Hope this helps.
Jacob