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

[davidblues]

Hi!

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

[Olly]

It's possible, using Power Pivot.

Load your data to the Data Model, then create a Measure:

Please Login or Register  to view this content.

See attachment for worked example.

[davidblues]

Hi, Olly.

Thanks so much for your response. Never worked with Power Pivot before, but thanks to your answer I've been getting started with it. I've been able to make it work in my own example. I even added another nesting level below with the same behaviour, and filtered it out with a second measure. However, I still can't make it work in my real case workbook. The measure syntax is ok and identical to the example, but it doesn't seem to perform the "blanking". I can't post the workbook here, but I'll keep working on it, see if I can figure out what is happening there.

Thanks again for introducing me to DAX, I have a feeling I'll be using it quite a lot -if I can make it work!

[davidblues]

It's possible, using Power Pivot.

Load your data to the Data Model, then create a Measure:

Please Login or Register  to view this content.

See attachment for worked example.

Forget about my previous message. I noticed that in my real workbook the empty fields are not blanks, but empty strings, so I changed ISBLANK ( VALUES ( Table1[Shape] ) ) for VALUES ( Table1[Shape] ) = "" and it worked fine

[Olly]

Well done! Welcome to the wonderful world of Power Pivot, and DAX

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[davidblues]

Well done! Welcome to the wonderful world of Power Pivot, and DAX

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks for the forum tips, will do inmediately. I'm used to other forums with the same interface as this, but lack those functionalities, so I didn't know they were present here. I'll keep it in mind for future posts.

Thanks again!