[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Excel Powerpivot relationships

Posted on 2011-05-12
Medium Priority
Last Modified: 2012-05-11

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
Question by:jandro33
  • 3
  • 2
LVL 10

Accepted Solution

itcouple earned 1000 total points
ID: 35759037

Normally (in powerpivot) you get 1 to 1 and 1 to many relationships. Which means that you can have in the parent table (sheet) only one unique value per column and in child table this value can appear multiple times (or not appear at all).

You might want to try create a separate sheet which will list all unique brands and then create the join.

For more information look for relational design (databases). It is the same concept.

Hope that helps

Author Comment

ID: 35760077
Could you suggest some resources that explain the basic principles of relational database design? Thank you.
LVL 10

Expert Comment

ID: 35760165
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
LVL 10

Expert Comment

ID: 35760214
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)


Author Closing Comment

ID: 35760238
Very helpful

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question