Using a Dimension Property as a Measure

Question

In my product dimension, I have an attribute called CustomerRating which is a string. Possible values are "1", "2", "3", and "4".

I want to turn this attribute into a Measure that averages ratings for collections of products.

with member [Measures].[Product Rating] as
( [Product].[Project Name].CurrentMember.Properties("CustomerRating"))
select [Measures].[Product Rating] on columns
from [MyCube]

This query produces an error - I suspect because I'm dealing with a string.
How do I turn customer rating into a measure that is an average rather than a sum?

Answer 1

Not tested but I'm wondering if this errors?

WITH 
   MEMBER [Measures].[Product Rating] AS
     CInt([Product].[Project Name].CurrentMember.Properties("CustomerRating"))
SELECT
   {[Measures].[Product Rating]} ON COLUMNS
From [MyCube]

Answer 2

The best approach would be to add an int column to the source table of the dimension, maybe just as a named calculation in the DSV. Then you would add a measure group on the dimension table, and define a measure rating_sum that sums this column, and a count measure in this measure group. Then define a calculated measure as rating_sum / count.

If everything works, make the two measures rating_sum and count invisible.