Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Analysis Services (2000)  Calculated Member

Author  Topic  smb Starting Member 2 Posts Posted - 2003-08-04 : 00:00:46 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! Anyone knows how to make the calculated memeber work even if user selects multiple time filters?