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.
prasad1211 Not sure I understand exactly what you are trying to do, but changed the PT so that it, at least, gives you the running totals as you want them to. Needed to add a sub-total at the "Material ID" level and collapse the field entirely.
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.
Thank you very much and I can live with not having Description or add it as a field outside using xlookup function. Need to understand the Pivot ways of working a bit more. Please suggest any material to study the mechanics.
Best regards
prassad
