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

[mediaman73]

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:


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:



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).



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?

 

[Chihiro]

Can you upload sample file? It makes testing solution so much easier and help us give you appropriate solution.

 

[mediaman73]

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.

 

[Chihiro]

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"?

 

[mediaman73]

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.

 

[Chihiro]

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).