Issue with Excel Pivot Table Subtotals / Row Hierarchy

0
votes

I currently have a pivot table with three row labels. The data in the row labels is grouped as follows:

Customer   SKU   Grouping  YTD   PrevYTD    
  123      ABC    Qty       30      20
                  Sales    500     400  

           DEG    Qty      100     150 
                  Sales    1,000   2,000

The YTD and PrevYTD are columns and the values.

I can do a custom subtotal on the 'Grouping' field and choose Sum and it gives me the totals at the end of the report:

                 Qty Sum   130      170
                 Sales Sum 1,500    2.400

This is good to this point, but I also want to have subtotals by customer. What I want to see is the same breakdown under each customer (a total of Qty and a total of Sales) but I cannot get this subtotal to work. When I put a subtotal on the Customer field, it puts a oneline subtotal and is adding the Qty and the Sales together. In this example if I put a subtotal on Customer This is what it will display:

      123 Total             1630    2570

Is this possible to do what I am looking for?

Thanks for the help!

2
excelpivotpivot-tablesubtotal
Easy alternative: just use a different pivot table. More complicated but better alternative: instead of pivot tables, set up a table using formulas. For this case, =sumifs(...) on the customer, sku, and grouping will do. If you show what the source data looks like, we can help set up the formulas for it. – vasek1

2 Answers

2
votes

How about making Grouping a column label instead? It will change the layout somewhat but will allow you to get the effect you are after.

0
votes

To do this you need to use a second pivot table. It's easier to do it with the tables side-by-side, but you can do it with a table underneath by just deleting the second table and inserting a new pivot table after the last row of the first each time the first table is changed.