Calculated Member help

Hi, I have a calculated member that computes the Sales per week for any given level. I am using a time dimensiona and a sales dimension. The time dimension has these levels: Year, Quarter, Month, and Weeks. I am using this formula

[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

The problem with this is that it works only if the user select a single time member. I am using Excel as frontend and I fixed the pivot table in such a way that Sales is on the column, and Store is on the Rows. The time dimension is on the top serving as filter (Page axis). Because you can choose multiple dimension member in the dropdown boxes of any dimension in Excel, I am having trouble making it work in these scenario. It only works if you select one time member at a time. Please help.I don't fully understand what you are trying to do, but since the database server is returning a simple dataset to your Excel pivot table, the problem would appear to be with the pivot table and not the query or table you are using. When you filter the pivot table you are fiiltering its stored snapshot of the query results. It does not pass the parameters back to the database server.

blindman|||Hi, thnks so much for the reply. Anyway, to make it clear to you:

I'm using an Analysis Service Cube as my backend and Excel Pivot table as my frontend.

I have two dimension in my cube: 1. Time(Yr, Qtr, Month, Week) and 2. Store

I have two measures: 1. Sales and 2. Sales per week (this measure is a calculated member

The formula I use to make the Sales per week measure is:
[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

Here's my setup in Excel: The sales is broken down by store and the time is on top acting as a filter

Time: 2003

Store Sales Sales/Week
Store1 100 25
Store2 200 12

My problem occurs evrytime the user selects more than one Time value to filter on (like all Jan AND Feb data). It works only if one time value is selected at a time. When user chooses more than 1 time value, the Sales/Week data appears as: #Num!|||Sorry smb, but I don't have experience with data cubes syntax. Hopefully now that your post is at the top of list someone else will check it out and give you an answer!

blindman|||Thnks anyway blindman :)