I have a data set with a structure in style of:
Fruit Type Organic Sales Banana 15 Apple Green Apple Organic 20 Apple Red Apple Non-Organic 25 Apple Red Apple Organic 17 Orange California 8 Orange Spain 5
In other words, the fruits have different levels of detail. Say I want to see sales for bananas 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 shows two rows of blank values for Banana, repeating the value of Sales. How can I make Pivot understand that blank values mean that the item is not specified further and it should stop listing values?
A picture to illustrate my point: