Relationships - 2 Many-to-Many with 2 Mapping Tables RRS feed

  • Question

  • I am struggling with my data model in Excel 2010 PowerPivot.  I have 4 tables.  2 have many to many and 2 look-up tables.   Both many to many tables are connected to each of the 2 look-up tables.   The look-up tables have 1 value

    I cannot change the relationship as PowerPivot forces the best direction (really frustrating)

    Table 1 (many) connected to Table 3 (1) by Country Name

    Table 1 (many) connected to Table 4 (1) by Supplier Name

    Table 2 (many) connected to table 3 (1) by CountryID

    Table 2 (many) connected to Table 4 (1) by Supplier Name

    Table 1 has currency values

    Table 2 has far less country names than Table 1

    I want to be able to sum the currency values from Table 1 against the Country Names found in Table 2. The issue is that the sum of currency values is the sum total from Table 1

    When I use Table 3 for the Country Names, the currency values from Table 1 works.  When I add the details from Table 2, then the currency values do not work.

    I have tried HASONEVALUE, HASONEFILTER, FILTER, and so on.... nothing works....  I know it is to do with the relationship, but I just cannot change it....  can you help me?

    Thank you

    Monday, November 21, 2016 4:59 PM


  • Hi AMDavies,

    You calculate the sum of currency values based on Country Name, right? If it is, you'd better create a calculated column in  using LOOPUPVALUE function. Then filter the table based on the Country Name column and calculated column.

    Or could you please share more details or sample data for further analysis.

    Best Regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 22, 2016 9:14 AM