help with powerpivoting from multiple tables

**fAdI** · 2013-11-29, 08:46 PM

any help on this is much appreciated:
how do I get the attached pivot table to work? I'm trying to get the '2013 estabs' column pull in same data point as in the '2012 estabs' . Now it is giving the total for the entire source column in the source table. In the powerpivot window, i made a primary key column and linked the two tables but it's not working. hope you see what i mean. the actual tables are 80,000+ rows each and want to have about 10 tables of that size linked.

fadi

**Ken Puls** · 2013-11-29, 11:35 PM

Hi Fadi,

The easiest way to deal with the scenario you are facing is actually to copy all of your 2013 data into your 2012 table, refresh the model, and drag the Year onto the columns. That should sort it.

In the grander scheme though, the challenge you are having is that your data sources need to be taken back into a normalized form. To make PowerPivot truly sing, you need to have a table that includes your columns A:M, as well as a Calendar table. You'd link both the calendar and the first table to your individual year's data and you should be able to report on what you need. As your data is currently set up though, it's going to cause you grief, I'm afraid.

**Herbds7** · 2013-11-30, 12:06 AM

Excel 2010 PowerPivot
With Paste Append
http://www.mediafire.com/view/datfdl...11_29_13a.xlsx

**fAdI** · 2013-11-30, 12:32 AM

Ok, thanks much. So answer is Copy and Past Append because of lack of normalization.
Though interesting there's no Append option when Get External Data From Text File in the PowerPivot window - It creates new table with each text file import which then needs to be copied and past Append to make one consolidated table.

**Herbds7** · 2013-11-30, 02:25 AM

Paste Append is just one method.
Another way is to create a relationship between the two tables
and tables that contain their unique values, such as dates, as Ken mentioned.
See PowerPivot2 in the same link.

**fAdI** · 2013-12-01, 12:01 AM

Thank you much. What a great exercise on database normalization which I needed. I recreated my workbook and followed your example on the various lookup tables and created the relationships but I didn't create a 'both' table and the 'Estabs' measure, and it is not working. Why do I need the 'both' table which seems to be the two tables appended if I have the relationships? I thought it was either combine the tables or create the relationships?

**Herbds7** · 2013-12-01, 04:17 AM

Divorced "Relationship" method from "Paste Append" method.
Start fresh. Don't try patching.
http://www.mediafire.com/view/3v588g.../11_30_13.xlsm

**fAdI** · 2013-12-01, 04:13 PM

I'm getting it.. I tried doing it without this Estab12 and Estab13 measures and it doesn't work - something about doesn't know the value of the field in this context, hence you need the sum operator on the individual fields in the tables. makes sense. once last question, what do you call or what's the logic behind the Estab measure that joins them together with the "+" in table1 (along with the fips field)?

**Herbds7** · 2013-12-01, 04:58 PM

The area_fips and area_title fields are redundant. Use either of both.
"+" means sum. Play with the Measures.
Here is another version using concatenate.
"Cat" can also be generated as a Calculated Column.
http://www.mediafire.com/view/4j4v3u.../11_30_13.xlsx