I've been wrestling with this for days now and I'm looking for some help. I originally posted the question on the following link, but I've gotten no responses. I'm not sure if it's because it's a dumb question or because I wasn't clear in my question. But here is the link, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3374157&SiteID=1

I'll also summarize the question here.

Basically, what the issue boils down to is this. I have a dimension and this dimension has a hierarchy defined. The default behavior is that the measures scoped at the parent level gets the sum of the measures scoped at the children level. Normally, this would be ok, but in my case, I want to turn this off. How would I do this?

Hi,

I know that this reply is coming a good two-and-a-half weeks later. But you could try this:

Set the aggregation for the measure to None instead of Sum. Then you need to setup some business intelligence by going to the Cube menu, select Add Business Intelligence. This would open up the appropriate wizard. Select the Create a custom rollup formula option in this wizard, next select the Parent-child dimension and then the attribute that defines the parent-child relationship. Select an appropriate source column (such as the Name attribute of the dimension) and you're done. Process the cube and check the results.

Note that you need to have a time dimension in order to get proper results, since the measure has become a non-additive one. Also, if the BIDS browser dosn't seem to show proper results try retrieving the data from Excel 2007. If you need I could give you a little sample of what I came up with.

Best Regards

Gogula G. Aryalingam (MVP - SQL Server)