Pivot Tables - Collecting Data from Multiple Tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1443457%22%20slang%3D%22en-US%22%3EPivot%20Tables%20-%20Collecting%20Data%20from%20Multiple%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443457%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20collect%20data%20on%20energy%20usage%20from%20several%20different%20tables%20that%20have%20location%2C%20time%2C%20and%20rate%20data%20associated%20with%20them%20and%20put%20them%20all%20in%20a%20pivot%20table%20that%20drills%20down%20from%20where%20the%20data%20was%20collected%20from%20(school%20location%20in%20this%20case)%2C%20to%20year%20and%20then%20monthly%20and%20daily%20energy%20use%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20a%20real%20hard%20time%20getting%20the%20pivot%20tables%20to%20keep%20locations%20when%20I%20add%20values%20to%20the%20data%20table.%20When%20I%20add%20energy%20data%20used%20in%20any%20school%20to%20the%20table%2C%20I%20lose%20all%20locations%20other%20than%20the%20first%20school.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20brand%20new%20to%20using%20the%20data%20model%20in%20Power%20Pivot%2C%20and%20could%20use%20some%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1443457%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1445250%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%20-%20Collecting%20Data%20from%20Multiple%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1445250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690392%22%20target%3D%22_blank%22%3E%40lmelton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%2C%20you%20will%20get%20a%20quicker%20and%20more%20helpful%20response%20if%20you%20post%20the%20workbook%20you%20have%2C%20just%20making%20sure%20it%20doesn't%20include%20any%20private%20or%20confidential%20information.%20Otherwise%20you'll%20only%20get%20fairly%20abstract%20principles%20or%20guidelines.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20is%20that%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448321%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%20-%20Collecting%20Data%20from%20Multiple%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20Here%20is%20the%20workbook.%20You'll%20notice%20that%20if%20you%20add%20any%20column%20from%20School_1%20or%20School_2%20that%20it%20will%20remove%20the%20other%20school%20from%20the%20School%20Summary%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448580%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%20-%20Collecting%20Data%20from%20Multiple%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690392%22%20target%3D%22_blank%22%3E%40lmelton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20able%20to%20make%20full%20sense%20of%20what%20I'm%20seeing.%20And%20it%20also%20appears%20that%20because%20I'm%20in%20the%20Mac%20universe%2C%20there%20are%20things%20that%20you%20make%20use%20of%20to%20which%20the%20Mac%20version%20of%20Excel%20has%20no%20access....so%20we'll%20have%20to%20await%20the%20assistance%20of%20some%20other%20Excel%20experts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20meantime%2C%20let%20me%20suggest%20that%20you%20add%20some%20commentary%20to%20each%20sheet%20of%20your%20workbook%20to%20describe--even%20briefly--what%20it's%20doing%2C%20what%20the%20source%20of%20the%20data%20is%2C%20what%20your%20specific%20difficulties%20are%20and%20which%20sheet(s)%20are%20the%20locus%20of%20those%20difficulties.%20Otherwise%20we're%20needing%20to%20make%20a%20lot%20of%20inferences.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am trying to collect data on energy usage from several different tables that have location, time, and rate data associated with them and put them all in a pivot table that drills down from where the data was collected from (school location in this case), to year and then monthly and daily energy use data. 

 

I'm having a real hard time getting the pivot tables to keep locations when I add values to the data table. When I add energy data used in any school to the table, I lose all locations other than the first school. 

 

I'm brand new to using the data model in Power Pivot, and could use some help.

3 Replies
Highlighted

@lmelton 

 

In general, you will get a quicker and more helpful response if you post the workbook you have, just making sure it doesn't include any private or confidential information. Otherwise you'll only get fairly abstract principles or guidelines.

 

So is that possible?

Highlighted

@mathetes  Here is the workbook. You'll notice that if you add any column from School_1 or School_2 that it will remove the other school from the School Summary worksheet.

 

Thanks for your reply.

Highlighted

@lmelton 

 

I'm not able to make full sense of what I'm seeing. And it also appears that because I'm in the Mac universe, there are things that you make use of to which the Mac version of Excel has no access....so we'll have to await the assistance of some other Excel experts.

 

In the meantime, let me suggest that you add some commentary to each sheet of your workbook to describe--even briefly--what it's doing, what the source of the data is, what your specific difficulties are and which sheet(s) are the locus of those difficulties. Otherwise we're needing to make a lot of inferences.