Pivot table - Running totals, Running % contribution calculations

[prasad1211]

Hi Experts,

I am trying to generate a simple Pivot table for a table with basic data of Product, Product description, Date of sales, Sale Qty and Sale Amount. Pretty common data I presume. I created a table format and inserted a pivot and trying to do summary calculations for data in 2019. (The data has 3 months of the year 2020. But I don't need this data). 

I would like to generate ABC, XYZ analysis table for products based on the sales value in Euro. I can get product sales summary across all the months, sorted on the yearly total and can calculate % of the contribution of each product to the total value sum of all the products. No problem so far. Life seems to be good with Pivots. 

Now ABC, I want to calculate running % contribution and I am struggling to get it from the pivot calculations using default options or using Calculated fields and items. Nothing is giving the desired running totals and running % of each product contribution to the sum of all products. Based on the cumulative %, would like to assign A (80% of sales), B (80-95% of sales), and C(95-100%). But I am stuck at the running totals and cumulative %. Not sure why I can't get these values from Pivot?

I am attaching my excel base sheet as well as half baked pivot table for your kind perusal.

Can experts help? 

[#2]

@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.

[#3]

@Riny_van_Eekelen 

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

[#4 · Accepted solution]

@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.

[#5]

@Riny_van_Eekelen 

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

[#6]

@prasad1211 Glad I could help. May I suggest you Google for "pivot table excel". You'll get numerous hits that can teach you about all the ins and outs of pivot tables.