OLAP + Pivot table + SumProduct

[JesseH77]

Having read the virtues of the SumProduct function I thought I'd put it to good use. My key data source is a Pivot Table connected to an OLAP data source.


The problem arises in that the data is not repeated for all the pivot fields


ie the data looks like:


Region------Store

West--------Random store 1

------------Random store 2

East--------Random store 3

------------Random store 4


If I want to sumproduct for Region="West" I only get one store.


Is there a Pivot table option to show all fields?


Jesse

 

[Hui]

Jesse

A helper column to the side which extracts the Regions and if blank gets the value above should help you proceed

 

[keymaster]

btw.. in excel 2010 you can extend row / column labels to blank cells..

 

[JesseH77]

I was hoping to avoid the helper column, I wanted a more elegant solution, I have many tables in my data set.


Good to know it's added in office 2k10.


Thanks for the help.


Jesse