calculate measure for a custom member set created dynamically using measure 2

[tlambert]

OK,
I'm stuck a bit.

I'm reporting off a a PowerCube with two fact tables
hospital discharge counts
income

They both use a common dimension - location (which has counties).

I want to know what the hospital discharge count is for counties grouped by income levels
<$25k, 25-50k, 50k+

I'm coming up short on seeing how to do this and I think I'm just missing something, or don't know how to search the web for the right thing.

To do this, I need to identify the county members that fall into each income category, and use this custom set of members to group the hospital data.

I've been trying to figure out how to use the member function to do this, but I'm coming up short.

Thanks in advance for any help!

My next step, which is less desirable, is to pull both measures into the query, create a new data item which groups them based on the income measure, and then creating a separate query just to total by the new data item. This is lots less efficient because of the other report requirements.

Tom

[MFGF]

I'm not sure I quite follow, sorry. It's a powercube, so all your measures are in one place, and all link to the same set of dimensions. Is that right so far?

Are you saying your cube has two measures and you're creating your own custom Exception Dimension using one of them (bucket values for the income measure)? Or are the Income buckets already members in a dimension?

Where I have done this before, the "buckets" have been built into a dimension in the cube (in Transformer), so I just nest these into the report to see the groupings I require.

I am struggling to think of how you might achieve these "buckets" in a dimensional report. I will ponder on it a while...

MF.

[tlambert]

Thanks, you're on the right track.

I can add levels to the dimension which group counties by income level, but this is a pretty static solution. There's literally dozens of income measures (alone) that come from the Census, so I'd have to create one group in the location dimension for each of them. Then there's other similar dimensions like education, and the fact that these things vary over time.

What I want to do, is model the census data by itself, so it has it's own measures. Then I can bring it into the report so users can create their own levels and their own bins. One person might want income brackets to be:
0-25k,
25-50k,
50k+

While another user wants:
0-20k,
20-40k,
40-60k,
60k+

The census has all kinds of income measures on the individual, community, and household levels, it's a never ending set of combinations and the more flexibility I can give to end users to be able to define bins on their own, the better. 

I want the user to be prompted for income bracket ranges to group counties by
bracket 1: [low] - [high]   :  name: 'low income'
bracket 2: [low] - [high]   :  name: 'medium income'
bracket 3: [low] - [high]   :  name: 'high income'

And the results would look something like this:
Geography                                                                Hospital discharge counts
Counties in user defined 'low income'                                     10000
Counties in user defined 'medium income' bracket                  5000
Counties in user defined 'high income' bracket                       1000

Again, this is more of a case of 'this is the type of stuff I want to do' and 'is there something I'm missing, because it seems like there should be functions for this'