Jan 07 2020 10:53 PM
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
Jan 08 2020 08:16 PM - edited Jan 09 2020 11:17 PM
@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.
Jan 09 2020 09:17 PM
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 |
Jan 09 2020 10:26 PM
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).