+ Reply to Thread
Results 1 to 12 of 12

Problem using data model in pivot table to drill down to records

  1. #1
    Registered User
    Join Date
    04-16-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    89

    Problem using data model in pivot table to drill down to records

    I have two datasets. One dataset is a list of customers and details about what kind of customer they are and where they are based geographically. The other is customer sales history. I want to create a relationship between these two worksheets so I can use the order history data to create different pivot tables from the customer details and then to drill down into the pivot tables to get a list of customers to contact.

    In this scenario I want to know the below:

    Which customers have never purchased anything?
    Which customers have purchased English-only?
    Which customers have purchased English and Maths?

    Because customers appear more than once in the order history worksheet I can't create a relationship from Customer details to Order History, only the other way round.
    Last edited by Excel_apprentice; 02-26-2022 at 11:06 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem using data model in pivot table to drill down to records

    By adding another column to the Orders data you can use a Pivot Table and a Slicer to select for the analysis you mention
    Make sure your Orders data has every customer listed with one blank row for each one that has not bought anything

    Use the SLicer to select one or more of the options

    See attached
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Problem using data model in pivot table to drill down to records

    Hi Apprentice and thanks for the question.

    I've been wanting to learn how to Merge Tables using Power Query and your problem is a perfect example. Richard did a VLookup to pull data from one table to the other but Merging tables is a newer and more robust method. What if you needed 3 or 4 tables to connect? Read this site: https://www.ablebits.com/office-addi....%20See%20More.
    and here is your workbook with merged tables. I'd bet you could also do the Pivot Table using Power Query, but that is another question.
    Merged relationships before pivot table.xlsx

    BTW - I'm not recommending Ablebits as an add-in, just the Power Query top half of the post linked above.
    Last edited by MarvinP; 02-26-2022 at 01:09 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    04-16-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    89

    Re: Problem using data model in pivot table to drill down to records

    Thank you, Richard and Marvin. Ideally, I need this to be pivoted from the customer details worksheet because the customers may show up more than once in the orders history worksheet and I'd like to avoid having to cleanse duplicates.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem using data model in pivot table to drill down to records

    The solution I offered didn't need any cleansing. It just needed an extra column with a formula on the Orders sheet.

    I agree Power Query is very powerful for doing all sorts of stuff where tables are quite different in their layouts. It does need a little more investment in time.

    I always prefer simplicity and this seems one case where adding a single column gets you what you want.

  6. #6
    Registered User
    Join Date
    04-16-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    89

    Re: Problem using data model in pivot table to drill down to records

    Quote Originally Posted by Richard Buttrey View Post
    The solution I offered didn't need any cleansing. It just needed an extra column with a formula on the Orders sheet.

    I agree Power Query is very powerful for doing all sorts of stuff where tables are quite different in their layouts. It does need a little more investment in time.

    I always prefer simplicity and this seems one case where adding a single column gets you what you want.
    Thanks for the reply. I'm really looking to display the data in customer details but use the data in order history to filter it. I perhaps didn't make that clear enough. So what I'd like is to have three pivots from the customer details page:

    Which customers have never purchased anything?
    Which customers have purchased English-only?
    Which customers have purchased English and Maths?

    I just couldn't work out how to do this without needing to run the check separately and add in those columns in the customer details worksheet.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Problem using data model in pivot table to drill down to records

    Hi,

    Where are your attempts at setting up the Pivot Tables in your file? I can't see a single Pivot Table in there. Also, it would be useful to clarify your expected results for those 3 questions you posed.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Registered User
    Join Date
    04-16-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    89

    Re: Problem using data model in pivot table to drill down to records

    I hadn't built them in this test data but have added now, I have a relationship between the two tables. There should be 5 customers with English. 1 customer with English and Maths. 2 customers with nothing.

    I'm not sure how to show the customers that have nothing or the customers that have both English and Maths.

    For the pivots I want to be able to drill down into to the pivot values and for it to display the customer order details column that has been filtered to only show the customers that have met that criteria.
    For the customers with English I've tried two different field settings. The first one has the right number of customers but when I drill down it is showing the order history data. The second pivot is showing the customer details data but has the wrong number of customers, it is counting all customers so not applying the filter.

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem using data model in pivot table to drill down to records

    Quote Originally Posted by Excel_apprentice View Post
    Thanks for the reply. I'm really looking to display the data in customer details but use the data in order history to filter it. I perhaps didn't make that clear enough. So what I'd like is to have three pivots from the customer details page:

    Which customers have never purchased anything?
    Which customers have purchased English-only?
    Which customers have purchased English and Maths?

    I just couldn't work out how to do this without needing to run the check separately and add in those columns in the customer details worksheet.
    Did you not open the file I sent? The PIvot Table and the associated Slicer allows you to filter the Pivot Table for the three requests you make - and more,

    To see customers who haven't purchased anything select the 'blank' item in the SLicer - The PT will show Wycombe Abbey Schools
    Click the 'English to see customers who have purchased English, and select both English and Maths to see those customers who have purchased both.

  10. #10
    Registered User
    Join Date
    04-16-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    89

    Re: Problem using data model in pivot table to drill down to records

    I did see it, but it's not quite doing what I want it to do. I want to see the customers by name and to be able to drill down into the pivots to create a worksheet. Your pivot shows the school name and code but not the customer name, and you can't drill down into the records.

    The real data I want to use this with is a very large file - around 30mb - and I've created it for other people to use as well, so keen to make it as easy to use as possible.

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Problem using data model in pivot table to drill down to records

    Quote Originally Posted by Excel_apprentice View Post
    I want to be able to drill down into to the pivot values and for it to display the customer order details
    When you drill down on an entry within a Pivot Table, you return details from the table which contains that field. In your case, this is the number of orders, and so must necessarily come from the Order history table.

    If you wish to overwrite this behaviour, so that you instead return details from the Customer details table upon drilldown, you will need to ensure that that table also contains the number of orders, which can only be done by creating an additional, physical column in that table which pulls that data in from the Order history table.

    Regards

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem using data model in pivot table to drill down to records

    Quote Originally Posted by Excel_apprentice View Post
    I did see it, but it's not quite doing what I want it to do. I want to see the customers by name and to be able to drill down into the pivots to create a worksheet. Your pivot shows the school name and code but not the customer name, and you can't drill down into the records.

    The real data I want to use this with is a very large file - around 30mb - and I've created it for other people to use as well, so keen to make it as easy to use as possible.
    It's a Pivot Table. You can include all of the fields from the underlying Data. See attached in which I've added the Contact name.

    I don't know what you mean by you can't 'drill down into the records'. The whole object of a PT is to do precisely that. The PT when filtered by using the SLICER DOES shows the underlying Records.

    You are not using any numerical data in this PT which would normally appear in the Values area of the field list and hence the PT is NOT summarising any data..

    Many people don't realise that when you are seeking the underlying records for a particular summary Value in a PT, double clicking on a value you're interested in immediately creates a new sheet showing an exact copy of all the records from the underlying data that contributes to that summary. But as I say that's not of any relevance in your PT since no summary is taking place.

+ 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. [SOLVED] Pivot Table Slicer Field names (Data model vs Non Data Model)
    By NewYears1978 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-02-2021, 10:52 AM
  2. [SOLVED] Code to drill pivot table data details in one sheet instead of drilling in multiple sheets
    By Elhadi Adel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-22-2021, 09:20 AM
  3. [SOLVED] Pivot Table with 2Million plus records? am i using the data model wrong?
    By adamtzvi in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-17-2021, 04:27 PM
  4. Replies: 3
    Last Post: 10-20-2019, 12:15 PM
  5. Replies: 3
    Last Post: 07-12-2019, 06:17 AM
  6. Creating a Pivot Table from Power Pivot Data Model?
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2017, 12:02 PM
  7. Pivot Table Drill Down Doesn't Match Master Data
    By ifdinc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-21-2017, 04:04 AM

Tags for this Thread

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