Hello all. Have a PT question I can't find a solution for. I may be overthinking it or going about it the wrong way but...
I have a data set that has item information broken into different segments. In this example, X, Y, Z. I'm using a PT to give me simple subtotals, % increases to last year, & actual difference to LY. What I also need is to calculate what % of that item's actual increase or decrease was to that segment's subtotal of LY.
For example, Item X1 sold 2 more units this year than last year. I need to understand what % the 2 unit difference made up of the overall -3.43% increase. To do that, you take the diff and divide it into the total LY sales for that segment; in this case, all of just the X's. Problem is, I can't get a formula to take the Diff column values for each X Item and divide it by the LY subtotal. I can get the '% of' option to work for either the total parent or grand total but I cannot figure out how to have each segment's diff divide into that segment's LY subtotal.
Can I accomplish this via a PT?
<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>
I have a data set that has item information broken into different segments. In this example, X, Y, Z. I'm using a PT to give me simple subtotals, % increases to last year, & actual difference to LY. What I also need is to calculate what % of that item's actual increase or decrease was to that segment's subtotal of LY.
For example, Item X1 sold 2 more units this year than last year. I need to understand what % the 2 unit difference made up of the overall -3.43% increase. To do that, you take the diff and divide it into the total LY sales for that segment; in this case, all of just the X's. Problem is, I can't get a formula to take the Diff column values for each X Item and divide it by the LY subtotal. I can get the '% of' option to work for either the total parent or grand total but I cannot figure out how to have each segment's diff divide into that segment's LY subtotal.
Can I accomplish this via a PT?
| Item | TY | LY | Increase | Diff | % of LY Subtotal |
| X1 | 50 | 48 | 4.17% | 2 | 0.86% |
| X2 | 75 | 60 | 25.00% | 15 | 6.44% |
| X3 | 100 | 125 | -20.00% | -25 | -10.73% |
| X Subtot | 225 | 233 | -3.43% | -8 | -3.43% |
| Y1 | 65 | 48 | 35.42% | 17 | 6.59% |
| Y2 | 200 | 175 | 14.29% | 25 | 9.69% |
| Y3 | 50 | 35 | 42.86% | 15 | 5.81% |
| Y Subtot | 315 | 258 | 22.09% | 57 | 22.09% |
| Z1 | 50 | 48 | 4.17% | 2 | 1.69% |
| Z2 | 15 | 10 | 50.00% | 5 | 4.24% |
| Z3 | 30 | 60 | -50.00% | -30 | -25.42% |
| Z Subtot | 95 | 118 | -19.49% | -23 | -19.49% |
<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>
