+ Reply to Thread
Results 1 to 7 of 7

Power pivot "relationships may be needed" for two data sets and one lookup table

  1. #1
    Registered User
    Join Date
    05-31-2016
    Location
    Auckland, New Zealand
    MS-Off Ver
    2016
    Posts
    6

    Power pivot "relationships may be needed" for two data sets and one lookup table

    Hello all,
    I'm a relatively new user of power pivot, and have been able to use it without too many problems so far but I'm currently stuck with creating a relationship between two sets of data using a single lookup table.

    Data set "NA" comes from same-structure files located in a folder, and files are added to this folder occasionally.
    Data set "EA" comes from same-structure files located in another folder, and files are added to this folder occasionally.

    In data set "NA" I have codes & names, and in data set "EA" I have codes and amounts. The codes appear multiple times in each data set.

    I want to display the codes, names and amounts in a single pivot table.

    My lookup table lists the codes as the unique value/lookup and I am trying to connect that code to each data set.

    When I manually create the relationship between NA code and Lookup code, then another relationship between EA code and Lookup code, it doesn't work.
    The result is repeated/identical values in my pivot table.

    Can anyone help please?

    Sample files attached.

    Thanks,
    Shannon
    Attached Files Attached Files
    Last edited by SLIM512; 02-07-2017 at 10:25 PM. Reason: Sample files attached now

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power pivot "relationships may be needed" for two data sets and one lookup table

    Hi,

    Which table's Code field are you using in the pivot table?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    05-31-2016
    Location
    Auckland, New Zealand
    MS-Off Ver
    2016
    Posts
    6

    Re: Power pivot "relationships may be needed" for two data sets and one lookup table

    Hi, thanks for your response!
    The code is present in both data sets and is the common link between the two. I want it to serve as a look up, but it appears multiple times in each data set. To get around the many-to-many issue, I thought I could create a lookup table with the codes as a unique list, then link the two data sets via the code on the lookup table.
    I have now attached some sample data.
    In reference to file "NA data set", I want to use column E (AV-Work Number) as the lookup code for column AB (AV-Work Number) in the "EA Data Set".
    I want the pivot table to show columns E and K from the "NA data set", and columns Y and AG from "EA data set".
    When I create the lookup table with the list of AV-Work Numbers as unique values, the try and link that to the AV-Work Number in each data set, the relationships are created, but I think they are going the wrong way (and I can't find a way to reverse them).
    Thanks again

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power pivot "relationships may be needed" for two data sets and one lookup table

    M2M is rarely simple in Power Pivot. Do you have Power Query available to you as well?

  5. #5
    Registered User
    Join Date
    05-31-2016
    Location
    Auckland, New Zealand
    MS-Off Ver
    2016
    Posts
    6

    Re: Power pivot "relationships may be needed" for two data sets and one lookup table

    Yes, I'm using Excel 2016 so both are available to me. I can use Power Query at a beginners level, but I'm certain I could be utilising it better.
    I've been working through some PQ tutorials to try and resolve this question but so far haven't figured it out!

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power pivot "relationships may be needed" for two data sets and one lookup table

    Hi again,

    You have multiple broadcaster values in NA for the same code. How do you intend for the amounts to be allocated across them? Simply duplicated?

  7. #7
    Registered User
    Join Date
    05-31-2016
    Location
    Auckland, New Zealand
    MS-Off Ver
    2016
    Posts
    6

    Re: Power pivot "relationships may be needed" for two data sets and one lookup table

    Hi again,
    Thanks for looking at my files.
    I'm looking to summarise the data so that I have a total amount per AV number, per broadcaster.
    In context I want to show the total revenue per broadcaster, per production (AV number). Issue being that the amounts are in one file, broadcaster in the other and the link/lookup is the AV number.
    So not duplicated - sum of amount across the the AV number+broadcaster.
    Thanks again!

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power pivot "relationships may be needed" for two data sets and one lookup table

    Let's consider work number 0000000001.

    In the NA file that appears with 6 rows. 2 for broadcasters QRS and YZ, and once each for TUV and WX.

    In the EA file, you have 2 rows with a total 'Share amount' of 53.55.

    Since your only link between the two is the work number, how do you allocate that 53.55 across the 4 broadcasters? The full amount for each one, or an equal share, or some other calculation?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-02-2016, 05:55 PM
  2. Replies: 6
    Last Post: 11-07-2015, 02:03 PM
  3. Replies: 0
    Last Post: 10-21-2013, 05:06 PM
  4. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  5. Replies: 0
    Last Post: 02-23-2013, 04:26 PM
  6. Replies: 3
    Last Post: 08-27-2009, 07:26 AM
  7. Replies: 0
    Last Post: 02-01-2006, 03:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1