I have a cube that tracks survey data. Products are rated on a 1 to 10
scale.
There is a Product dimension and the rating scores are in the Fact table.

Is there a way to create a calculated member that counts the number of times
a score of 9 or 10 is given to a particular product?

I then want to divide this number by the total number of reviews for that
product...possible?

try to create a "Rate" dimension which is the values from 1 to 10, you can
group these values if you want (like 9-10 group and 1-8 group)

your fact table will be:
ProductID ; RateID ; DateID

Your measure is a simple count, your cube has 3 dimensions (product, time
and rate)

now the user can see how many evaluation of a particular rate a product has.
the ratio will be a calculated measure:

measures.[Eval count] / (measures.[Eval count], rates.[All Rates])



"Rob" <Rob (AT) discussions (DOT) microsoft.com> wrote

Just because your ratings are in the fact table, doesn't mean that the
individual ratings will end up in your cube -- you will need to define an
aggregation for a measure, and you will get a sum, average, or some other
aggregation of your ratings, that you can slice/dice by the dimensions in
your cube. If product is your only dimension, you can only see the sum (or
whatever aggregation you choose) of the ratings *by Product*, not the actual
ratings themselves.

A notable exception is that if, indeed, your dimensions are designed so that
the lowest grain of the dimensions is at the same level as the individual
rows in the fact table -- then the sum of the ratings at that level will be
the actual ratings themselves (no aggregation). You can do this with a
degenerate dimension, which basically includes a member for each of your
fact table records, although this isn't really an efficient way to do what
you're trying to do -- consider having the ratings as both a dimension and a
measure. Using a degenerate dimension, you can define the calculated measure
as follows:

with member [Measures].[NumberOfHighRanks] as
'count(filter([DegenerateDimension].[FactID].Children,([Measures].[Rating]
{[Product].Children} on rows
from [Cube]

--RJ

"Rob" <Rob (AT) discussions (DOT) microsoft.com> wrote