Forum Discussion
Custom subtotal in a pivot table
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
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.
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 1 Size 2 Size 3 Size 4 Size 5 Size 6 TOT Zznordstrom 74 126 159 134 95 588 Swimwear Galore 4 8 8 6 26 American Beauty Group 1 3 2 2 1 9 Beachpool 2 2 2 2 8 Aktivworx NZ 1 2 1 4 Portique at Shoal Bay 1 1 1 1 4 Mix N Match 1 1 1 1 4 Hot Body 1 1 1 1 4 P`Chi Swimwear 1 1 1 1 4 Shekki Perth 1 1 1 3 Shekki Broome 1 1 2 SUTOTAL 84 145 129 151 97 656 CUSTOM SUB TOTAL 105 181 161 199 121 820 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).
