This should not be done in the cube,It should have already been done in sql.What this will do is multiply the aggregation of product price by the aggregation of order quantity, whereas you should be aggregating (productprice * orderquantity). So create a new column in your fact table containing a value representing (ProductPrice * orderquantity) and add this column to your measure group with an aggregation type of sum, that would be a start. After that we canlook at the mdx query.

Thank you so far!!!Now I have a new column of sales in Measures representing Price*Quantity.Although I still don't know how to separate the Saturdays given that I only have Date, Year, Quarter, Month, Monthname, Day in my Time Dimension.Maybe with Filter? Or do you know how to restrict dates similar to SQL??

WITH MEMBER Measures.AverageSales
AS
AVG(FILTER(DESCENDANTS([Date].[Month].CURRENTMEMBER,[Date].[Day]),[Date].[Day].&[Saturday]),[Measures].[Sales])
SELECT Measures.AverageSales ON 0,
NONEMPTY([Date].[Month].CHILDREN) ON 1
FROM [CubeName]

Here Sales will be field containing Price*Quantity and I'm assuming Date dimension will have Month, day etc as fields in natural Hierarchy

The problem is I cant modify the original tables in SQL.Any idea how to create my needed weekday thing in SSaS as a 'new named calculation' or query... in order to create a new column with all weekdays pulled from Date or DateID?!

The problem is I cant modify the original tables in SQL.Any idea how to create my needed weekday thing in SSaS as a 'new named calculation' or query... in order to create a new column with all weekdays pulled from Date or DateID?!

you can use a named query in SSAS data source view (or add a view in sqlserver itself). My recommended way is latter one where i'll include in view a computed column based on date field to get day equivalent

Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.

Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.

Not something i prefer as its not scalable enough. Tomorrow if they want Monday sales it will cause addition of another columns etc which means metadata changes too often. I would prefer to keep just day as an attribute in date dimension for all these and based on requirement just tweak the MDX to filter on correponding day attribute. Even if they want an interactive analysis based on day parameter they can utilize the field and write a MDX expression based STRTOSET() etc functions

Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.

Not something i prefer as its not scalable enough. Tomorrow if they want Monday sales it will cause addition of another columns etc which means metadata changes too often. I would prefer to keep just day as an attribute in date dimension for all these and based on requirement just tweak the MDX to filter on correponding day attribute. Even if they want an interactive analysis based on day parameter they can utilize the field and write a MDX expression based STRTOSET() etc functions

Yes agreed Visakh, but like I said, it can also be a decision based on performance. STRTOSET is an expensive on query resources and you not going to get better performance than a measure that is already pre aggregated.

Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.

Not something i prefer as its not scalable enough. Tomorrow if they want Monday sales it will cause addition of another columns etc which means metadata changes too often. I would prefer to keep just day as an attribute in date dimension for all these and based on requirement just tweak the MDX to filter on correponding day attribute. Even if they want an interactive analysis based on day parameter they can utilize the field and write a MDX expression based STRTOSET() etc functions

Yes agreed Visakh, but like I said, it can also be a decision based on performance. STRTOSET is an expensive on query resources and you not going to get better performance than a measure that is already pre aggregated.

yep...thats true But i would prefer to keep changes at the data model level a bare minimum and provide flexibility at front end.Especially if front end being a reporting tool definitely you might have to use query based on methods like STRTOSET etc to cater to interactive user inputs.