1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Showing Row Header w/ No Data, w/o Detail

Discussion in 'Ask an Excel Question' started by mediaman73, Nov 27, 2017.

  1. mediaman73

    mediaman73 New Member

    Messages:
    3
    This is a bit finicky.

    When I pull a pivot with filtration, I want to show all row headers, but maintain the filtration for details, and aggregate data.

    So, for instance, let's say I have the following data set:

    upload_2017-11-27_13-54-31.png
    If I pull a pivot for this data set and filter only on "Sub-Sub-Head 1" for data, I get the following pivot table:

    upload_2017-11-27_13-55-45.png

    This entirely excises "Header 2" from the pivot. Of course, the natural response is "Well, show 'no data' items in the pivot, then."

    However, if I go into the row header field to "Show items with no data" it pulls every sub-head under that column, even if it doesn't exist within that row header (as below).

    upload_2017-11-27_13-58-47.png

    If you'll recall, my data set for "Header 2" only had 2 records, but this pivot is now showing all variables within column B of my data set, whether it existed within that row header or not.

    My question: Is it possible to ONLY show a row header in a filtered pivot for a value that has no data associated? If I "Show items with no data" when a filter is applied, must I ALWAYS show all associated data points for records associated with the filtered pivot?
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,247
    Can you upload sample file? It makes testing solution so much easier and help us give you appropriate solution.
  3. mediaman73

    mediaman73 New Member

    Messages:
    3
    Sure! Just uploaded the test file I used to show the screen grabs. First tab is a standard pivot with filter. Second is the same pivot, but with "Show items with no data" turned on.

    Attached Files:

  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,247
    Ah, this is intended behaviour for normal pivot.

    Unfortunately there isn't a way to filter out non-existent items when "Show items with no data" is turned on.

    There are ways to work around it, by transforming source data. But I'm bit unclear on your desired output.

    When filtered on "Sub-Sub-Head1" what should be displayed? And when it's filtered for "Sub-Sub-Head2"?
  5. mediaman73

    mediaman73 New Member

    Messages:
    3
    Well, my goal is to show different pivots pulling from the same data set for multiple views, depending on whether the viewer is interested in the equivalent of "Sub-Sub-Head1" vs. "Sub-Sub-Head2."

    And, rather than having the equivalet of "Header 2" entirely missing from a pivot, simply showing that row header with no data beneath it.

    Were I to show a visualization, I would want to show all data categories, and then just show zeroes under those with no data beneath it. It's the difference between a zero and null value. I'm basically hoping to do the equivalent in a pivot table.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,247
    But you don't have any line item for Sub-Sub-Head 1 under Header 2.

    If you want to show something, you either need to add zero/null value line to source data range for... Header 2, Sub-Head 1/2 & Sub-Sub-Head 1.

    Edit: This can be done in PowerQuery or in Data model (or in Excel sheet if you don't have PowerQuery or PowerPivot).

Share This Page