Forum Discussion
Pivot table - Running totals, Running % contribution calculations
prasad1211 Right-click on any item in the "Material ID" column and select "Expand all fields". Then all material descriptions become visible again, and a (sub)total for each "material ID" is shown. But your table doesn't look how you want it. The sub-total is part of the Design ribbon (while a cell is selected inside the PT).
What this shows you is that the "Show value as - Running Total In" is applied to the first row header "Material ID". Excel takes the sub-totals for each material ID to create the running total. Difficult to explain in words.
If the material description is unique for every material ID, just leave it out of the PT that creates the running totals (example attached). Add the descriptions back from a master Materials table, using one of the LOOKUP functions available in Excel.
This is a brilliant answer. Thank you very much.
We couldn't get despite burning midnight oil during the weekend.
Two clarifications
1. Material description is missed in the pivot. Material and Material descriptions are the two fields that must be displayed for the user to search.
2. To my small mind, I don't get the steps of adding the sub-total row and collapsing. What is the governing logic?
best
prasad
prasad1211 Right-click on any item in the "Material ID" column and select "Expand all fields". Then all material descriptions become visible again, and a (sub)total for each "material ID" is shown. But your table doesn't look how you want it. The sub-total is part of the Design ribbon (while a cell is selected inside the PT).
What this shows you is that the "Show value as - Running Total In" is applied to the first row header "Material ID". Excel takes the sub-totals for each material ID to create the running total. Difficult to explain in words.
If the material description is unique for every material ID, just leave it out of the PT that creates the running totals (example attached). Add the descriptions back from a master Materials table, using one of the LOOKUP functions available in Excel.