Results 1 to 4 of 4

Thread: Show products by supplier

  1. #1
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus

    Show products by supplier



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have connected Power Pivot to two tables (in the data model).
    Table 1 = Suppliers with Supplier ID as primary key
    Table 2 = Products with Supplier ID as foreign key

    I connect the tables in Power Pivot on the key fields.

    In The pivot table, I add the Supplier Key and Supplier name to the Rows Area
    Then I add the Product Name from the Products Table to the Area field

    What I get is ALL products showing for ALL suppliers. (I was expecting to see all suppliers with only the products they sell - due to the link)

    Is this supposed to happen?
    How can I only see relevant products per supplier in the pivot?

    TX

    Attached is the sample workbook.

    Attachment 3415
    Regards,
    Rudi
    www.eileenslounge.com

  2. #2
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    I think I found out that I need to use the RELATED() function. So effectively one needs to pull in all fields into one of the tables and pivot only on that one table?! Please inform if I am wrong!! I was under the impression that I can (since I have a defined link) pull fields from both tables into the pivot and it will display related to the key fields.

    I look forward to any feedback!
    TX
    Regards,
    Rudi
    www.eileenslounge.com

  3. #3
    Excel 2013 Pro Plus with PowerPivot.
    What you got is expected behavior if you leave the Values field blank.
    Supplier ID in the Row field should come from the Products table.
    Otherwise you are wrong on all counts.
    Since PowerPivot is non-intuitive, a book might help.
    http://www.mediafire.com/view/jj992p.../04_23_15.xlsx

  4. #4
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    TX Herbds7
    You've confirmed a few good things for me.
    Regards,
    Rudi
    www.eileenslounge.com

Posting Permissions

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