Many-to-many Relationship Error

[tsolis13]

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!

 

[Aladin Akyurek]

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.

 

[tsolis13]

Thank for your reply, unfortunately I am not quite sure as to how to go about normalizing the data efficiently.

 

[Jerry Sullivan]

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.

 

[Jerry Sullivan]

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.

 

[tsolis13]

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!!