Custom subtotal in a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-1096081%22%20slang%3D%22en-US%22%3ECustom%20subtotal%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1096081%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20pivot%20table%20with%20subtotal%20showing%20sales%20across%20various%20sizes%20running%20along%20a%20row%20%2CI%20wanted%20to%20add%20a%20line%20below%20this%20showing%20a%20custom%20subtotal%20ofeach%20value%20a%20%2B%2025%25.%20So%20another%20words%20if%20the%20sub%20total%20for%20one%20size%20was%20100%2C%20below%20it%20I'd%20like%20to%20see%20125%20etc%20etc%20for%20each%20value%20in%20the%20row.%20Thanks%20Eqa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1096081%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100599%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20subtotal%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERow%20LabelsSum%20of%20U1Sum%20of%20U2Sum%20of%20U3Sum%20of%20U4Sum%20of%20U7Sum%20of%20U8Sum%20of%20TotalUnitsSL1221CB%20-%20FRILL%20MULTIFIT%20ONE%20PIECE%20WITH%20LADDER%20LACE%20TRIM%26nbsp%3B%20-%20NIGHT%20SKY%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BNIGHT%20SKY%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BNIGHT%20SKY%20Total8414517915197%26nbsp%3B656SL1221CB%20-%20FRILL%20MULTIFIT%20ONE%20PIECE%20WITH%20LADDER%20LACE%20TRIM%26nbsp%3B%20-%20NIGHT%20SKY%20Total8414517915197%26nbsp%3B656%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESize%201%3C%2FTD%3E%3CTD%3ESize%202%3C%2FTD%3E%3CTD%3ESize%203%3C%2FTD%3E%3CTD%3ESize%204%3C%2FTD%3E%3CTD%3ESize%205%3C%2FTD%3E%3CTD%3ESize%206%3C%2FTD%3E%3CTD%3ETOT%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EZznordstrom%3C%2FTD%3E%3CTD%3E74%3C%2FTD%3E%3CTD%3E126%3C%2FTD%3E%3CTD%3E159%3C%2FTD%3E%3CTD%3E134%3C%2FTD%3E%3CTD%3E95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E588%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESwimwear%20Galore%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAmerican%20Beauty%20Group%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBeachpool%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAktivworx%20NZ%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPortique%20at%20Shoal%20Bay%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMix%20N%20Match%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHot%20Body%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EP%60Chi%20Swimwear%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EShekki%20Perth%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EShekki%20Broome%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESUTOTAL%3C%2FTD%3E%3CTD%3E84%3C%2FTD%3E%3CTD%3E145%3C%2FTD%3E%3CTD%3E129%3C%2FTD%3E%3CTD%3E151%3C%2FTD%3E%3CTD%3E97%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E656%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3ECUSTOM%20SUB%20TOTAL%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E105%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E181%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E161%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E199%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E121%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E820%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100656%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20subtotal%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100656%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F264774%22%20target%3D%22_blank%22%3E%40Eqa33%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20a%20revised%20version%20of%20the%20%22Calulated%20Item%22%20option%20in%26nbsp%3B%3CSPAN%3Emy%20example.%20Close%2C%20but%20probably%20not%20workable%20as%20you%20would%20have%20to%20change%20both%20Sub-total%20lines%20when%20you%20add%20or%20delete%20products.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EUnless%20someone%20comes%20up%20with%20an%20idea%20how%20to%20solve%20this%20within%20the%20area%20of%20the%20pivot%20table%2C%20I%20suggest%20you%20%26nbsp%3Bpick-up%20the%20total%20and%20perform%20the%2025%25%20calculation%20outside%20the%20pivot%20table%20(also%20in%20the%20example).%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1097883%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20subtotal%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1097883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F264774%22%20target%3D%22_blank%22%3E%40Eqa33%3C%2FA%3E%26nbsp%3BIt%20all%20depends%20on%20how%20your%20pivot%20table%20looks%20like.%20Can%20imagine%20that%20you%20have%20rows%20with%20products%20and%20sales%20figures%20per%20size%20in%20columns.%20You%20can%20insert%20a%20so-called%20Calculated%20Field.%20This%20creates%20extra%20columns.%20Alternatively%2C%20you%20insert%20a%20Calculated%20Item%20for%20every%20single%20product.%20The%20Calculated%20Item%20option%2C%20probably%20come%20closest%20to%20what%20you%20asked%20for%2C%20but%26nbsp%3Bneither%20option%20is%20particularly%20elegant%2C%20in%20my%20opinion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20attached%20a%20workbook%20with%20examples%20of%20how%20it%20could%20look%20like.%20If%20this%20is%20totally%20different%20from%20your%20actual%20situation%2C%20you%20need%20to%20be%20a%20bit%20more%20specific.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a pivot table with subtotal showing sales across various sizes running along a row ,I wanted to add a line below this showing a custom subtotal ofeach value a + 25%. So another words if the sub total for one size was 100, below it I'd like to see 125 etc etc for each value in the row. Thanks Eqa

3 Replies
Highlighted

@Eqa33 It all depends on how your pivot table looks like. Can imagine that you have rows with products and sales figures per size in columns. You can insert a so-called Calculated Field. This creates extra columns. Alternatively, you insert a Calculated Item for every single product. The Calculated Item option, probably come closest to what you asked for, but neither option is particularly elegant, in my opinion.

 

Have attached a workbook with examples of how it could look like. If this is totally different from your actual situation, you need to be a bit more specific.

Highlighted

@Riny_van_Eekelen 

Row LabelsSum of U1Sum of U2Sum of U3Sum of U4Sum of U7Sum of U8Sum of TotalUnitsSL1221CB - FRILL MULTIFIT ONE PIECE WITH LADDER LACE TRIM  - NIGHT SKY       NIGHT SKY       NIGHT SKY Total8414517915197 656SL1221CB - FRILL MULTIFIT ONE PIECE WITH LADDER LACE TRIM  - NIGHT SKY Total8414517915197 656

 Size 1Size 2Size 3Size 4Size 5Size 6TOT
Zznordstrom7412615913495 588
Swimwear Galore4886  26
American Beauty Group13221 9
Beachpool2222  8
Aktivworx NZ 121  4
Portique at Shoal Bay 1111 4
Mix N Match1111  4
Hot Body1111  4
P`Chi Swimwear1111  4
Shekki Perth 111  3
Shekki Broome  11  2
SUTOTAL8414512915197 656
CUSTOM SUB TOTAL105181161199121 820
Highlighted

@Eqa33 

Attached a revised version of the "Calulated Item" option in my example. Close, but probably not workable as you would have to change both Sub-total lines when you add or delete products.

 

Unless someone comes up with an idea how to solve this within the area of the pivot table, I suggest you  pick-up the total and perform the 25% calculation outside the pivot table (also in the example).