Power Pivot Help

[daysleeper15]

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.

 

[GraH]

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]

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]

Enjoy the study.