Sep 20 2018 08:13 PM - edited Sep 20 2018 08:14 PM
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
Sep 21 2018 06:56 AM
Sep 23 2018 05:43 PM
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.
Dec 14 2021 03:57 PM
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.
Dec 15 2021 12:07 PM
Afraid that doesn't work, if only (blank) replace with any other text within PivotTable
Dec 15 2021 04:54 PM
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.
Dec 16 2021 12:52 AM
@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.
Dec 18 2021 09:28 AM
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.
Dec 20 2021 01:55 AM
Jan 06 2022 12:26 PM
Would you please describe your solution?
Mar 16 2022 12:18 PM
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!