Pivot tables with nested fields when not all values have same level of nesting

davidblues

New Member
Joined
Jan 8, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I'm an experienced user of VBA in Excel, bur pretty new to pivot tables. I've been catching up lately, and I'm even gonna have to use Power Query extensively, but so far there's this thing I can't figure out how to solve. I've been researching, but I either I can't hit the right search string or the topic really hasn't been addressed. Maybe because it's too simple or straight impossible to do, but in any case, I haven't found anything related.
I have a big and complex set of data (over 400000 rows after unpivoting the original range) that needs to be summarised in a pivot table. There are some nested fields, but some values in those fields have different levels of further nesting. It's difficult to express with words, so I produced a simple reduced example, which can be seen in this picture:

TablesExample.png


In the example, the idea is to replicate the simple plain table in the upper right corner, but with a pivot table. There are only two colours for chairs, black and grey, and three for tables, black, brown and white. But for the tables there's an extra field regarding its shape, which can be square or round. When I build a pivot table to view the total sales per category, I have a couple of empty rows under the black and grey labels for chairs, as can be seen in table 1. Those rows have to go for the table to be ready for publishing, but I can't figure out how. If you collapse the field "Colour", the collapse happens for all the values of the parent field, as you can see in table 2, so I can't use that. I could stack two tables as in table 3, but that would only work in compact layout, and I can't get rid of the headers in the second table, so that I could get the effect of having only one. Besides, as soon as I modified something in the first table in the stack that implied adding some rows, Excel wouldn't let me do it because it would invade the second table in the stack. The goal would be to have one single pivot table with the looks of the stack in table 3. Any ideas? Is it even possible?

Thanks in advance,

David
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
If you right click on the pivot Got to Field Settings, Layout & Print, Then choose Show item labels in Tabular form, does that help?
 
Upvote 0
Thanks, but nope. Apparently there's nothing in the regular table/field/value options that can do the trick, I'm gonna have to start dealing with Power Pivot and DAX measures to take those rows out.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top