# Flattened Power Pivot Table



## Mavericks334 (Mar 18, 2013)

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.

DateHeading2_CodeHeadcountAttrition9/1/20083310ED77869/1/20083310FL227869/1/20083310HD117869/1/20083310PZ3786

<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>

</TBODY>

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 (Mar 18, 2013)

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


----------

