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 units.
(Values in spreadsheet different from text example)
Please kindly suggest solutions and formula for it to align.
Greatly appreciated.
Thank you.
1) Incorrect view with =Iferror(getpivotdata) - Current (Sample Values in text example)
2) Desired State - Assumed there are many slicer fields not shown.
Example: Products Selected in Slicer (Units "DEF" + FY17+19)
3 ) Pivot Table with No Slicers Active "Baseline" (Sample Values)
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 units.
(Values in spreadsheet different from text example)
Please kindly suggest solutions and formula for it to align.
Greatly appreciated.
Thank you.
1) Incorrect view with =Iferror(getpivotdata) - Current (Sample Values in text example)
"Artificial" Grand Total with Iferror(Getpivotdata) | 0.00 | 0.00. | 10000 | 0.00 | 0.00 |
2) Desired State - Assumed there are many slicer fields not shown.
Example: Products Selected in Slicer (Units "DEF" + FY17+19)
Grand Total (Above Artificial) | Formula to Pickup Subtotal (Changes with Slicer Input) = 2200 | Formula to pickup subtotal (changes with slicer inpt) = 65000 | 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 |
DEF Paid | DEF PO | |||||
FY17 | 1200 | 5000 | ||||
FY19 | 1000 | 60000 | ||||
Grand Total (From Pivot) | 2200 | 65000 | ||||
PAID | PO Amount | |||||
ABC | DEF | OPS | ABC | DEF | OPS | |
FY17 | 1000 | 2000 | 5000 | 7000 | 1000 | 10000 |
FY18 | 2000 | 2000 | 1000 | 2000 | 5000 | 20000 |
FY19 | 3000 | 4000 | 1000 | 2000 | 20000 | 50000 |
Grand Total | 6000 | 8000 | 7000 | 11000 | 26000 | 80000 |
Attachments
Last edited: