Hide (blank) row headings/subtotals in pivot tables

[Alistair Wallace]

Hi Team,

I am looking for a solution to blank heading/subtotal rows in a pivot table with a multi level hierarchy. A label filter is an easy enough solution providing the (blank) label value is at the lowest level of the hierarchy, however if the (blank) label value is in one of the higher "grouping" levels the label filter will remove all the rows in the (blank) group.

In the attached chart of accounts there are 5 grouping levels, with 1 being the highest grouping level (Income Statement) and 5 being the individual posting account level. Where there is no grouping required for any intermediate level (level 2 to level 4) a subtotal for "(blank)" is created in the pivot table.

Is there a way to remove the heading/total for the (blank) categories from the pivot table, or is there a way to structure the chart of accounts to achieve this outcome, or a VBA solution, or are there any other alternatives that will get the result I am after? I am open to suggestions.

Thanks for your time.

Regards,

Alistair

[#2]

Click on the filter dropdown in A1. Select the field "Reportlevel2" in the dropdown of the filterpane and then uncheck "(Blank)". Press OK. Repeat for field "Reportlevel3".

[#3]

Thanks JKP, however if a filter is used to exclude "(blank)" for "ReportLevel2" then all the child records of "(blank)" are filtered out of the report. All the records need to remain in the report, it is just the heading and subtotal that I want removed.

If you open the file and do what you have suggested, the total of the income statement changes. The total of the income statement needs to remain the same.

Cheers.

[#4]

Hi @Alistair Wallace 

Did you ever find a solution to this? I am grappling with the exact same problem. Would love to know what solution you found, if any.

Many thanks.

[#5]

@ShogunPatch 

Afraid that doesn't work, if only (blank) replace with any other text within PivotTable

[#6]

Hi @ShogunPatch, sorry mate no solution. You can structure the data so there is a value for each hierarchy, or live with the blanks, or not use a pivot table. I have been living with the blanks.

[#7]

@Alistair Wallace , @Sergei Baklan OK, thank you both for your replies.  This must be a fairly common scenario/problem, so I'm amazed Microsoft haven't designed a solution to address this issue - it seems like a fairly simple and basic requirement.  Still, if that's the way it is then I guess I, too, will have to live with the blanks.  

Many thanks.

[#8]

@ShogunPatch 

Requirement could be simple but solution is quite complex. To exclude from hierarchy levels with empty string in name is the same as to exclude level with any other text as name. 

Actually you need to rebuild hierarchy on the fly moving next levels upper if current level has empty string in name. Perhaps there are DAX samples, not sure.

[#9]

@SergeiBaklan
I've been mulling my problem over and experimenting with my dataset since my last post and arrived (independently) at the same conclusion. In fact, I managed to do what I needed (I think - I'm still experimenting somewhat!) relatively simply using M code inside the power query which originally did the ETL on my data.

[#10]

@ShogunPatch 

Would you please describe your solution?

[#11]

@Alistair Wallace

I just tried a bit of a workaround, and seemed to work. If you use 'Conditional Formatting', then 'New Rule', and Select Rule Type: "Use a formula to determine which cells to format". 

Use a formula of: 

=IF(B15="(blank)",1,0)

then apply the formatting to have the font color be the same color as the background (ie white font on a white background appears 'empty').

Good luck!