Many-to-many Relationship Error

tsolis13

New Member
Joined
Dec 21, 2015
Messages
12
Hello all.

I am new to Power Pivot and am taking my first stab at it by uploading multiple data sets that show net revenue received for each company. I am having a difficult time creating a relationship between the net revenue columns for each data set. Instead, I receive an error saying "Net Amount is a many-to-many relationship which is not currently supported." What is the most efficient way I can get around this error?

Thanks!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You would need to normalize your data. Example:

Man <<--->> Woman

becomes

Man <-->>Partner<<-->Woman

Thus: many-to-many must be organized as 2 x one-to-many relations.
 
Upvote 0
Thank for your reply, unfortunately I am not quite sure as to how to go about normalizing the data efficiently.
 
Upvote 0
Hi tsolis,

Can you explain why you want to create a relationship between Net Revenue columns?

Typically when relating multiple tables, the relationships are made between qualitative properties (e.g. Names, Locations, Dates) instead of quantitative properties like Net Revenue.

For your scenario, the relationship would more likely be between Company Names that two or more tables have in common. Since the data tables you are trying to link probably have multiple listings of the same Company, a Lookup Table of unique Company Names is often employed. The lookup table has a one to many relationship with each table it supports.

When you relate a table of data for 2017 sales to the company lookup, and a table of data for 2016 sales to the company lookup, you can build a model that allow comparing or aggregating the information in the two tables.
 
Upvote 0
One other note, you might consider using Power Query (Get & Transform) instead of Power Pivot. I find it much simpler for most operations involving pulling together data from multiple sources.
 
Last edited:
Upvote 0
Ultimately my goal, is to build a consolidated report listing revenue received by company for each entity we own, organized by month for the year 2018. I have already created a LookUp table for Company and Dates and that manages to show up on my pivot table successfully. However, when I add 'Net Amount' into my Values Field for my pivot table, I get zeros all across. Whenever I try to add 'Net Amount' in for all companies, I get multiple 'Sum of Net Amount' columns per month instead of just one per month. I am hoping to use this table for ongoing purposes, but I am not sure if it will require constant tweaking if I create a data table for 2017 revenue, 2018 revenue, etc. when every month I will be updating this table with current month revenues.

Oddly enough, I have never messed with Power Query and this is my first time in years attempting to create a table using Power Pivot. I know, I am probably learning everything backwards!!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top