Hey there...

I am using SSAS 2005 and I have a fact table for tracking types and
amounts of Revenue that organizations have. It is set up like so:

CompanyID, Revenue, RevenueTypeID, RevenueRangeID

The RevenueTypeID and RevenueRangeID fields point to Dimension tables
with values for types (Premier, Regular) and ranges ($0 - $25K, $25K -
$50K, et).

When browsing in the cube, as long as I specify a RevenueType, the
results sum correctly. But when I choose to just browse by Company and
RevenueRange, everything gets screwed up. This is because a company can
have more than one Revenue Type, but the RangeID is just limited to
that row, not the sum of rows for that company. Like this:

Org1 25K Type: Premier Range: 1
Org1 75K Type: Regular Range: 3

So if I am trying to just organize Companies by RangeID, Org1 will
probably show up twice. What I really need is the Range to be 4 (25K +
75K = 100K, which would fit in $75K - $100K or Range 4). Is there a way
to do this in MDX or some calculated member?

Ideally, I wouldn't even have a RevenueRangeID field in the fact table.
I could still have my Revenue Range dimension, but have some way to sum
the Revenue measure and see if it falls between Min and Max attributes
of the dimension. Any thoughts?

Thanx!

J'son