Hi Everyone,
I've exhausted all of my knowledge on trying to find a solution to this and hoping someone else has a workaround. The attached data is only fake data but I have thousands of rows of data to manipulate and I need to find an answer. I've google searched for days and still cannot find a solution.
I cannot seem to figure out how to address the subtotal error with calculated fields in a pivot table when the calculation is a percent. See image below.
BU (Multiple Items)
Sum of Amount Year Month
2017 2017 Total 2018 2018 Total Grand Total
Metric Product 1 2 1 2
Sales Apples $15,580 $25,420 $41,000 $16,047 $28,700 $44,747 $85,747
Peach $32,185 $18,450 $50,635 $33,231 $21,771 $55,002 $105,637
Sales Total $47,765 $43,870 $91,635 $49,278 $50,471 $99,749 $191,384
COM Apples $6,090 $6,090 $12,180 $8,242 $6,273 $14,515 $26,695
Peach $15,225 $7,105 $22,330 $21,680 $14,332 $36,012 $58,342
COM Total $21,315 $13,195 $34,510 $29,922 $20,605 $50,527 $85,037
GP$ Apples $9,490 $19,330 $28,820 $7,806 $22,427 $30,233 $59,053
Peach $16,960 $11,345 $28,305 $11,550 $7,439 $18,989 $47,294
GP$ Total $26,450 $30,675 $57,125 $19,356 $29,867 $49,222 $106,347
GP% Apples 60.9% 76.0% 137.0% 48.6% 78.1% 126.8% 263.7%
Peach 52.7% 61.5% 114.2% 34.8% 34.2% 68.9% 183.1%
GP% Total 113.6% 137.5% 251.1% 83.4% 112.3% 195.7% 446.9%
Grand Total 95531.13607 87741.37533 183272.5114 98556.63398 100943.1231 199499.7571 382772.2685
GP total for Apples 70%
GP total for Peaches 56%
Bookmarks