+ Reply to Thread
Results 1 to 4 of 4

Un-nest Multiple Pivot Table Rows

  1. #1
    Registered User
    Join Date
    02-25-2021
    Location
    Louisville KY
    MS-Off Ver
    2016
    Posts
    2

    Unhappy Un-nest Multiple Pivot Table Rows

    I have 3 columns of criteria that pull from our system that have to possible options of Yes or a blank cell if they don't meet that criteria. I want these to show up in the pivot table as columns so that we can see the subtotals for the values showing in the pivot table, but when I move the 3 criteria to the rows section of the pivot table, they are nested in the groupings rather than being on the same "level". Is there any way to change this? I also don't want to show the rows for blank cells (so for any of the entries that don't meet the criteria and have a blank cell). I can't change the way the data pulls from our system, so any workarounds really have to be in excel.
    excel 1.png excel2.png
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Un-nest Multiple Pivot Table Rows

    You should flatten out your data, rather than in cross tab structure.

    Ex: Using helper column in data source.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then add [Product Name] & [Helper] to Rows field, [Count of Business Name] (caption # Apps) and [Sum of Amount] in Values.

    If you want to remove blanks, just use Row Label filter. Where Helper <> "".

    If you need to keep the total including blanks, that will require OLAP based pivot table using DAX measure.
    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
    Registered User
    Join Date
    02-25-2021
    Location
    Louisville KY
    MS-Off Ver
    2016
    Posts
    2

    Re: Un-nest Multiple Pivot Table Rows

    The only problem I'm having with this solution is that sometimes businesses will meet 2 of those criteria (for example being both VOB and WBE). What can I do to make sure all criteria are accounted for?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Un-nest Multiple Pivot Table Rows

    Can you provide sample?

    It will be much easier for me to help you then.

+ 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. Overall Rank in Pivot Table with Multiple Rows
    By CrocHntr in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-09-2018, 10:28 AM
  2. Pivot table with multiple rows against the same column headers
    By bocgaroe in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-17-2018, 05:37 AM
  3. Pivot Table with multiple rows/sub rows
    By j_cole in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-01-2016, 07:05 AM
  4. Multiple Metrics in Pivot Table Rows - Help!
    By zorn in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-19-2015, 10:56 AM
  5. Replies: 1
    Last Post: 11-29-2012, 06:24 PM
  6. Pivot table multiple rows/data
    By MADRE in forum Excel General
    Replies: 0
    Last Post: 02-25-2009, 10:44 PM
  7. Pivot Table Repeats Names in multiple rows
    By Riccardo in forum Excel General
    Replies: 2
    Last Post: 09-16-2005, 03:05 PM

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