SeveralTradesLater
New Member
Hi Smart Folks, new member here.
I have a file with a fairly lengthy Pivot Table and Slicers I want to add a "artificial" grand total row on top of the pivot.
I am currently using =iferror(getpivotdata) to capture to grandtotals from the bottom of the pivot, but it doesn't adjust with the slicers and doesn't align with the products.
Please kindly suggest solutions.
Thank you.
Incorrect view with =Iferror(getpivotdata) - Current
Desired State - Assumed there are many slicer fields not shown.
Example: Products Selected in Slicer (Banana and Strawberries + FY17+19)
Pivot Table with No Slicers Active "Baseline"
I have a file with a fairly lengthy Pivot Table and Slicers I want to add a "artificial" grand total row on top of the pivot.
I am currently using =iferror(getpivotdata) to capture to grandtotals from the bottom of the pivot, but it doesn't adjust with the slicers and doesn't align with the products.
Please kindly suggest solutions.
Thank you.
Incorrect view with =Iferror(getpivotdata) - Current
"Artificial" Grand Total with Iferror(Getpivotdata) | 0.00 | 0.00. | 7.00 | 0.00 | 0.00 |
Desired State - Assumed there are many slicer fields not shown.
Example: Products Selected in Slicer (Banana and Strawberries + FY17+19)
Grand Total (Above Artificial) | Formula to Pickup Subtotal (Changes with Slicer Input) = 6.00 | Formula to pickup subtotal (changes with slicer input) = 12.00 | 0.00 (Fields Should be zero when no products are below, but if additional product is selected should reflect new subtotal based on slicers) | 0.00 | 0.00 | 0.00 |
Banana | Strawberries | |||||
FY17 | 5.00 | 2.00 | ||||
FY19 | 1.00 | 10.00 | ||||
Grand Total (From Pivot) | 6.00 | 12.00 | ||||
Pivot Table with No Slicers Active "Baseline"
Apple | Orange | Banana | Peach | Kiwi | Strawberries | |
FY17 | 1.00 | 2.00 | 5.00 | 7.00 | 1.00 | 2.00 |
FY18 | 2.00 | 2.00 | 1.00 | 2.00 | 5.00 | 10.00 |
FY19 | 3.00 | 4.00 | 1.00 | 2.00 | 1.00 | 10.00 |
Grand Total | 6.00 | 8.00 | 7.00 | 11.00 | 7.00 | 22.00 |