Data Model Pivot - Prevent Text from Subtotaling

DodoBird

New Member
Joined
Apr 25, 2018
Messages
4
I have an advanced question in regards to Pivot tables that I cannot seem to find an answer for anywhere (if someone can point me in the direction of an already existing thread that would do wonders).

I use my Pivot table (in Data Model mode) to do a summary of my Company financials. I had to use the Data Model, as there are comments in each program that must be shown in the Pivot "value" section.
The issue I have run into is the constant reformatting I must do to the chart due to the subtotal and grand totals concatenating my comments (Mock data attached for reference. Yellow comments are required, but red fields are redundant).

Currently I format those particular cells to show the same background and font color to mask the issue (ie. hide the text), but this is now a file that is accessible to anyone and it is causing unnecessary frustration...

I have tried the following:
- Unselect Subtotal and Grand total
- Problem with this is that I still need the values (ie. profits) to subtotal
- Change font color to match background
- As soon as new data is entered, these formatting is now showing in the wrong cells
- Keep the comments outside of the Pivot chart, and do a Vlookup to pull the comments (ie. keep it separate from the pivot table altogether)
' - The formatting for the report is now unusable since all comments are now at the bottom of the page

Fastest solution would be an option to stop subtotaling on certail fields, but it seems that this option disappeared as soon as the Pivot was made in a Data Model....

Much appreciated for any help.
Thank you
 

Attachments

  • Screenshot 2020-10-19 120637.jpg
    Screenshot 2020-10-19 120637.jpg
    129.2 KB · Views: 10
Last edited by a moderator:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,118,313
Messages
5,571,501
Members
412,398
Latest member
TsaeOps
Top