+ Reply to Thread
Results 1 to 8 of 8

Problem in merging 3 tables into 1 pivot table

  1. #1
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Problem in merging 3 tables into 1 pivot table

    I have 3 table (Stock_tbl, Sales_tbl and Cust_tbl). I create a pivot table and source as Stock_tbl. And click the "add to data model"
    After thet, i create the relation between the 3 table. Using Order No as the Common Column. HOwever, my pivot table is not correct. It just show the me the Total and does not show me the amount in each Order No. What went wrong??
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Problem in merging 3 tables into 1 pivot table

    If you double click on an amount in the PT (ie. Cell C6), it will open a new worksheet with the details of that amount
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Problem in merging 3 tables into 1 pivot table

    Ya i know, what i mean is that how come my pivot table wont show the amount/details as per Order no. Even if i filter out 1 Order No. It still show all the grand total of all customer and sales person, instead of the respective customer and sales person.

  4. #4
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Problem in merging 3 tables into 1 pivot table

    I discover i get the error if i Drag something from Stock_tbl to the field list. Duno y aso. Hope some expert can advice.

  5. #5
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Problem in merging 3 tables into 1 pivot table

    Sorry, i see wrong. I still get the same result no matter how i drag...

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Problem in merging 3 tables into 1 pivot table

    To achieve this effect, combine tables with Power Query
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Problem in merging 3 tables into 1 pivot table

    No way to do this using Relationship and the data model??

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Problem in merging 3 tables into 1 pivot table

    In the attached, I used Get and Transform to combine the three tables joined on the Order number and then built a pivot table.
    Attached Files Attached Files

+ 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: 2
    Last Post: 02-02-2012, 09:49 AM
  2. merging pivot tables?
    By certain_death in forum Excel General
    Replies: 0
    Last Post: 04-03-2009, 02:19 PM
  3. Merging Pivot tables
    By rambhadra in forum Excel General
    Replies: 4
    Last Post: 03-14-2007, 12:56 PM
  4. Merging 2 different pivot tables
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] Merging 2 different pivot tables
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] Merging 2 different pivot tables
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. Merging 2 different pivot tables
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. [SOLVED] Merging 2 different pivot tables
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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