Hierarchical data structure in pivot - blank rows

[Myggenx]

Hi there

So I have a dataset with a structure in style of:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Fruit Type Organic Sales
Banana 15$
Apple Green Apple Organic 20$
Apple Red Apple Non-Organic 25$
Apple Red Apple Organic 17$
Orange Califonia 8$
Orange Spain 5$

</code>In other words the fruits have different levels of detail. Say I want to see sales for Banana and organic green apples, this would require me to put Fruit, Type and Organic in row labels and Sales in Values. However the problem arises in that Pivot does show 2 rows of blank values for Banana, repeating the value of Sales. I want Pivot to somehow understand that when that blank values mean that the item is not specificed further and it should stop listing values. I have attached a picture to illustrate my point.

http://i62.tinypic.com/ezh6oj.png

Any suggestions are much appreciated!

 

[Asala42]

Right click on the pivot table and go to Pivot Table Options. On the display tab check off the "Classic PivotTable layout".

This will put each category grouping in it's own column, so you can hit the drop down arrow on each sub category and hide the blanks.

 

[scottsen]

Something like:
=IF(ISBLANK(Table[Level3]), BLANK(), [level 3 total])

 

[Myggenx]

Scottsen, where should i put this formula?

 

[scottsen]

Oops! Since we are in the Power Pivot forum, I assumed you were using Power Pivot... happens frequently

Since you seem to NOT be using Power Pivot, you will probably get more response in the main excel forum. Though, maybe Asala42's answer is good for you?