If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Tuesday, January 6, 2015

Power Pivot Table (Excel 2010)

Power Pivot is a free add-in to the 2010 version and can be downloaded from below link. It comes with Office 2013 Pro & Office 365.
Download Link: https://support.office.com/en-nz/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045
Power Pivot extends capabilities of the pivot data summarization and we can import data from multiple data sources.
The following free Microsoft Virtual Academy course can be very helpful in learning Power Query & Power Pivot along more info.
http://www.microsoftvirtualacademy.com/training-courses/faster-insights-to-data-with-power-bi-jump-start
In this post, I will share a basic use of Power Pivot with example in Excel 2010.
Once we have downloaded the add-in, we would be able to see it in our workbook in top ribbon.
With Power Pivot, we can link our multiple tables through the common Key/Column between them. It’s just like the relational database. Once we have linked them, then they could be summarized at one place. We will see it in this post step by step.
We have the below 3 tables of data in the 3 separate sheets.
Customer Table
KeyCustomer
Name
Gender
Type
State/Province
306256
Mary
Female
Individual
Punjab
306257
Sally
Female
Individual
Sindh
306258
Asif
Male
Individual
KPK
306259
Arif
Male
Individual
Punjab
306260
Nadeem
Male
Individual
Sindh
306261
William
Male
Company
KPK
306262
Michael
Male
Company
Punjab
306263
Natasha
Female
Individual
Sindh
306264
Praveen
Female
Individual
KPK
306265
Nazar
Male
Company
Punjab


Product Table
ProductKey
P-Name
Price
Size
P-123
T-Shirt
350
Large
P-124
T-Shirt
300
Medium
P-125
T-Shirt
250
Small
P-126
Hockey Stick
800
Large
P-127
Hockey Stick
750
Medium
P-128
Hockey Stick
700
Small
P-129
Tennis Racket
1000
Large
P-130
Tennis Racket
950
Medium
P-131
Tennis Racket
800
Small
P-132
Bag
1000
Large
P-133
Bag
950
Medium
P-134
Bag
800
Small


Sales Table
SalesID
Date
KeyCustomer
ProductKey
Quantity
S-111
12/1/2014
306256
P-123
2
S-112
12/1/2014
306257
P-124
3
S-111
12/1/2014
306258
P-125
1
S-112
12/1/2014
306259
P-126
4
S-111
12/1/2014
306260
P-127
5
S-112
12/1/2014
306261
P-128
3
S-111
12/1/2014
306262
P-129
2
S-112
12/2/2014
306263
P-130
6
S-111
12/2/2014
306264
P-131
9
S-112
12/2/2014
306265
P-132
1
S-111
12/3/2014
306261
P-133
2
S-112
12/10/2014
306262
P-134
2
S-111
12/15/2014
306263
P-126
2
S-112
12/16/2014
306261
P-127
3
S-111
12/17/2014
306262
P-128
1
S-112
12/18/2014
306263
P-129
4
S-111
12/19/2014
306265
P-131
5
S-112
12/20/2014
306261
P-132
3
S-111
12/21/2014
306262
P-133
2
S-112
12/22/2014
306263
P-131
6
S-111
12/23/2014
306261
P-132
9
S-112
12/24/2014
306262
P-133
1
S-111
12/25/2014
306263
P-133
2
S-112
12/26/2014
306259
P-134
2
S-111
12/27/2014
306260
P-125
2
S-112
12/28/2014
306261
P-126
3
S-111
12/29/2014
306262
P-127
1
S-112
12/30/2014
306263
P-134
4
S-111
12/31/2014
306264
P-126
5
S-112
1/1/2015
306265
P-127
3


Have your data in table form, for this select your data and press CtrlL. Name your table, just for clarity. For this have your cursor somewhere in table and go to design tab and change the default table name there.
After that click on the PowerPivot which would be at the top bar. Your cursor be in somewhere the table i.e Customer Table and then click on Create Linked Table. This will move the table in Power Pivot window. Do this for all the tables and move the 3 tables.



Now Go to Design Tab in Power Pivot window and then click on Create Relationship, a new window will open.
Relate the Product and Sales table on ProductKey Column and Customer and Sales Table on KeyCustomer Column, one by one.




This way all the three tables are linked now.
For any change in relationship click on Manage Relationship
To view the Diagram View of relationships, go to Table Tools tab and then click on Diagram View.




Once the relationship is finalized, go to workbook, PowerPivot then PivotTable and Create Pivot table as we do, but here we can have data from all the 3 tables in our Pivot table.