Totals and subtotals in pivot table

1
votes

Pivot table data has the following structure: # Person A Quantity # ## Category X 10 ## ## Category Y 20 ## ## Category Z 25 ## # Person A Total 55 # # Person B # ## Category X 30 ## ## Category Y 15 ## ## Category Z 0 ## # Person B Total 45 # # Grand Total 100 #

Is it possible to have Grand Total in the structure shown below: # Grand Total 100 # ## Category X 40 ## ## Category Y 35 ## ## Category Z 25 ## Just grand totals by the values of second labels in pivot table rows. enter image description here Pic1 as it is now. enter image description here Pic2 as required.

1
excelpivot-tablepowerpivot
what are you field names ? - PeterH
Person / Category / Quantity - Adamz
Not in the same pivot in the way you have shown. Your bottom few rows you have shown are without person in the row field whereas the top is with. You cannot simultaneously have this field present and not present, except in in some Schrödinger universe. - QHarr

1 Answers

0
votes

I can get to this:

enter image description here

...by first turning this:

enter image description here

...into this:

enter image description here

Which I then use for the Pivot Table:

enter image description here

I chose for Subtotals to "Show all Subtotals at Top of Group" and turned Grand Totals "Off for Rows and Columns".

I used Power Query to make the new table that I used for the Pivot Table. For that, I used the original table (Table1) as the source. Then I did Transform -> Group By, with these settings:

enter image description here

Then I did Add Column -> Custom Colum, with these settings:

enter image description here

Then I did Home -> Append Queries, with these settings:

enter image description here

Then, in the formula bar, I changed this:

enter image description here

...to this:

enter image description here

#"Changed Type" points to the earlier Applied Query Step that was the last "version" of the original table. This appends the most recent "version" (#"Added Custom") with it. It appends the #"Added Custom" "query" to the #"Changed Type" "query".

Then Home -> Close and Load to close Power Query and load the new table into a new tab in your spreadsheet. That's the table to use for your Pivot Table.