Pivot tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1240207%22%20slang%3D%22en-US%22%3EPivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1240207%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20Pivot%20table%20for%20an%20accounting%20profit%20and%20loss%2C%20i%20have%205%20account%20hierarchy's%20in%20rows.%26nbsp%3B%20I%20would%20like%20to%20exclude%20a%20level%205%20line%20item%2C%20when%20i%20deselect%20it%20from%20the%20filters%20%2F%20details%20it%20doesn't%20show%20when%20the%20rows%20are%20full%20expanded%20but%20when%20i%20collapse%20the%20rows%20even%20by%201%20hierarchy%20to%20level%204%20it%20adds%20the%20value%20of%20that%20item%20back%20into%20the%20total%2C%20how%20do%20i%20excluded%20from%20all%20hierarchy's%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1240207%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268417%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F587749%22%20target%3D%22_blank%22%3E%40KPPWM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EHave%20you%20try%20excluding%20filter%20elements%20from%20totals%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20click%20on%20your%20pivot%20table%2C%20select%3A%20PivotTable%20Options.%3C%2FP%3E%3CP%3EOn%20the%20Totals%20%26amp%3B%20Filters%20tab%2C%20unsleect%20the%20option%20%3CSTRONG%3E%22Include%20filteres%20items%20in%20set%20totals%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAnd%20also%20uncheck%26nbsp%3B%20%3CSTRONG%3E%22Include%20filtered%20items%20in%20totals%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pivot%20totals.PNG%22%20style%3D%22width%3A%20460px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180935i5A9250902F04DCB1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Pivot%20totals.PNG%22%20alt%3D%22Pivot%20totals.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have a Pivot table for an accounting profit and loss, i have 5 account hierarchy's in rows.  I would like to exclude a level 5 line item, when i deselect it from the filters / details it doesn't show when the rows are full expanded but when i collapse the rows even by 1 hierarchy to level 4 it adds the value of that item back into the total, how do i excluded from all hierarchy's?

 

 

1 Reply
Highlighted

@KPPWM 

Hi,

Have you try excluding filter elements from totals?

 

Right click on your pivot table, select: PivotTable Options.

On the Totals & Filters tab, unsleect the option "Include filteres items in set totals"

And also uncheck  "Include filtered items in totals"

 

Pivot totals.PNG