Power Pivot Help

daysleeper15

New Member
Joined
Jan 9, 2007
Messages
35
Hi, can someone please tell me if I’m thinking about this right?

I work with large data sets that are refreshed quarterly. Part of my job is to summarize the data by company, division or region, etc. and then run a couple of reports based on the summarized data, ie, quarter over quarter change in balance and I need to add or subtract amounts to certain balances which I put together in separate tables.

In the end I usually have 5 or 6 separate pivot tables that I can’t seem to make talk to each other so I copy and paste the tables and do vlookups and sumifs to get to a final table that I need.

It seems to me like I should be able to use power pivot to connect these tables (somehow and skip the vlookups). Is that the intended purpose of PowerPivot and how best to learn the basics of this tool? I have to say I’m an experienced excel user so I thought that if I watched a couple of videos on YouTube I would be able to pick up power pivot but I haven’t hit the right one yet, I’m just left very confused after watching the video and I’m having the hardest time figuring out how to even get started w/ power pivot, it’s so frustrating.

Anyway, thanks in advance.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
613
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,
This depends on the (source) data tables you have. I won't make a data model based on pivots. It might well be you can make all the measures you need with the proper data model based on your source tables.

Other alternative is Power Query to make your lookups easier and combine your pivots in a single table. Again depending on the number of data it might not be advised to do that.

There are some great books out there, and they are not so expensive (Rob Collie, Matt Allington, I would not start with the books from the Italians Marco Russo and Alberto Ferrari. They are the reference when it comes down to in depth knowledge of DAX). I also like the (free) video tutorials by Mike Girvin. There is a whole series on DataModels/PowerPivot/DAX.
 

daysleeper15

New Member
Joined
Jan 9, 2007
Messages
35
Thanks for your reply, right after I posted my message I bought “analyzing data with power bi and power pivot for excel” by Russo and Ferrari. Then I read your post and bought “Power Pivot and Power BI: The excel user’s guide to DAX, Power Query, Power BI & Power Pivot” by Collie and Singh.

I’ll start with the later and get through both over the next couple of weeks. Hopefully this will get me a strong foundation and I can move a bit closer to that 4 hour work week I’m shooting for.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
613
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Enjoy the study.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,798
Members
410,708
Latest member
SanTrapGamer
Top