+ Reply to Thread
Results 1 to 4 of 4

Pivot Table and Data Model

  1. #1
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Broooklyn, NY
    MS-Off Ver
    Excel 2016
    Posts
    207

    Pivot Table and Data Model

    I need to create a pivot table but it doesn't work for me. The main thing is that I need to do open and close count by regional manager and indicate percentage but it doesn't work for me. Any ideas? Also, I was wondering if I could record pivot tables as macro.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Pivot Table and Data Model

    Couple of issues with your raw data layout.

    1. Merged cells.

    It's never good idea to use merged cell. Avoid it at all cost. Except when no further data transformation, analysis are performed on the data.
    It's nothing, but a visual fluff.

    2. Cross tab structure of date columns.

    I'd recommend unpivotting date columns. Though for your specific case, it isn't necessary. However, if you want to do any sort of time intelligence, it would be good idea.

    Also, while it is possible, the way you want the report laid out isn't really meant for Pivot. Since you are not performing any aggregation on the data at all.
    As well, it's never a good idea to stack Pivot Tables in single page. As data increases, it may cause pivot range to overlap and will require manual adjustment of positions.

    Attached is sample set up for Pivot with each Regional Manager reported in separate sheet. Or you could just use 1 report and use filter to control segment (if you have Excel 2010 or later I'd recommend Slicer).
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Broooklyn, NY
    MS-Off Ver
    Excel 2016
    Posts
    207

    Re: Pivot Table and Data Model

    It is more like a dashboard, so the numbers have to be calculated after each regional manager. Can I add calculated field to the slicer?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Pivot Table and Data Model

    You can add Regional manager as left most field in Row Label (i.e. top level) and add subtotal only for that field.

    0.JPG

+ 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. For each pivot item in pivot table - for data model
    By dancing-shadow in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-23-2017, 02:45 PM
  2. Add this data to the Data Model addition needed to current pivot table code
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2017, 06:52 PM
  3. VBA filter Pivot Table basen on Data Model
    By zico8 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-04-2017, 04:31 AM
  4. 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
  5. Selecting Column Data in Pivot Table (from Data model)
    By derickz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2016, 11:35 PM
  6. power pivot table - Data model
    By pccamara in forum Excel General
    Replies: 2
    Last Post: 04-18-2016, 11:20 AM
  7. [SOLVED] Grouping disabled when pivot table is added to the data model?
    By Rvsalka in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-17-2015, 04:56 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