Link to home
Start Free TrialLog in
Avatar of jandro33
jandro33

asked on

Excel Powerpivot relationships

Hello,

I am trying to learn powerpivot and am having some issues establishing relationships between tables. I have three tables:
1. Sales data, which contains monthly sales in dollars and quantity and gm  by brand
2. Deductions which contains coop and other deductions by brand
3. Returns which contains product returns by brand

I tried to link them by brands, but I get a message that says that duplicate values exist. I attached a small sample.

Any help would be appreciated. Thank you.

Alex Book6.xlsx
ASKER CERTIFIED SOLUTION
Avatar of itcouple
itcouple
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jandro33
jandro33

ASKER

Could you suggest some resources that explain the basic principles of relational database design? Thank you.
The basic principle is you have one table (sheet) that stores one unqiue instance of the table. For instance if you have Product table then each row would be unique product with its attributes (properties describing the product like name, description) then let say you have order table and orderline table where you follow the same principle. You need to make them talk with each other and you do that by putting productID (primary Key) into orderline table (product + order = unique row) so based on this ID you can "lookup" all properties of this product in product table (name, description etc)

For more info might be best to google denormalization process see here more info http://en.wikipedia.org/wiki/Database_normalization

PowerPivot seems to be something between excel + database and the problem is that database is very different in structure to excel so you might have to slightly change your thinking (important is structure not layout)

Hope that helps
Emil
Just a thought..... once you get your head around relational design you probably will want to go with start schema http://en.wikipedia.org/wiki/Star_schema (as opposed to snowflake)

Regards
Emil
Very helpful