Ratio Calculation based on calculations from two different dimensions

I am trying to come up with a way to calculate a ratio that is based on the values from two different dimension calculations. The values are based on how a person responded to a question on a survey. I have one calculation that I can get with the following MDX statement:

select {Measures.[Brand Usage Last Try 2-6]} on columns,

non empty([brand usage].[brand label].children) on rows

from cube

RESULTS:

Brand Usage Last Try 2-6

Brand 1

164.277

Brand 2

208.513

Brand 3

131.193

(The [Brand Usage Last Try 2-6] measure is the Measures.Weight aggregate calculation for people that responded with a value from 2 to 6 for a particular brand).

The second calculation is the following:

with member measures.hrd as ([Brand Awareness].[VarResponse].[Var3].&[hrd].&[heard_of_brand], [Measures].[Weight])

select {measures.hrd} on columns,

non empty([brand awareness].[brand label].children) on rows

from cube

RESULTS:

hrd

Brand 1

301.601

Brand 2

462.385

Brand 3

361.533

I need to be able to take the calculation from the first one and divide it by the value for the second calculation based on brand. I tried using the linkmember to retrieve the values based on the brand, but it is not returning the results that I expected.

with member measures.hrd as ([Brand Awareness].[VarResponse].[Var3].&[hrd].&[heard_of_brand], [Measures].[Weight])

member measures.hrdlnk as (measures.hrd, linkmember([Brand Usage].[Brand Label].currentmember,[Brand Awareness].[Brand Label]))

member measures.ratio as (iif(measures.hrdlnk > 0, measures.[brand usage last try 2-6]/measures.hrdlnk, null)*100)

select {measures.[Brand Usage Last Try 2-6], measures.hrd, measures.hrdlnk,

measures.ratio} on columns,

non empty([brand usage].[brand label].children) on rows

from cube

RESULTS:

Brand Usage Last Try 2-6

hrd

ratio

Brand 1

164.277

301.601

54.47

Brand 2

208.513

(null)

(null)

Brand 3

131.193

271.332

48.35

Somehow the first brand ratio calculation comes out right, but that is the only one. Both of these dimensions reference a brand dimension. Each dimension, [Brand Awareness] and [Brand Usage], contain questions and potential responses for each type of brand in the brand dimension. Both dimensions have a hierarchy that I created [VarResponse] in them that is based on the type of question [Var3] (which is the first three characters of the Variable name) and then the response [Category Name].

"Both of these dimensions reference a brand dimension. Each dimension, [Brand Awareness] and [Brand Usage], contain questions and potential responses for each type of brand in the brand dimension." - could you explain how dimension usage is configured for the cube, in terms of these 3 dimensions and the [Measures].[Weight] measure group? Assuming that [Brand] has a regular relation to the fact table, and that [Brand Awareness] and [Brand Usage] each has a many-to-many relation to the measure group, there may be unintended interaction when computing hrdlnk, so try this:

member measures.hrdlnk as (measures.hrd, linkmember([Brand Usage].[Brand Label].currentmember, [Brand Awareness].[Brand Label]), [Brand Usage].[Brand Label].[All])

|||

This calculation did work and produced the results that I was looking for, thank you so much.

In regards to the configuration, the brand table is linked to each of the dimension tables, awareness and usage. The brand table does not directly relate to the fact table. The dimension tables, awareness and usage have a regular relationship to the fact table and they both have a key designated in the table. This is how it is currently configured.

Brand (Brand_ID) -->Brand Awareness (Brand_Awareness_ID, Brand_ID)

-->Brand Usage(Brand_Usage_ID, Brand_ID)

-->Fact Table(Date_ID, Respondent_ID, Brand_Awareness_ID, Brand_Usage_ID)

The granularity within the fact table is not what I am typically used to, so this is kind of an odd setup.

Rate Table - Need Help To Display Data Horizontally

Hi everyone. I need help creating a query for a transportation company. I am trying to querying a table of rates that stores it's values vertically and want to display the information horizontally.

Below is an example of sample data from the table. One column in the table lists all the different cities that a group of breakpoints apply to; another lists the different weight breakpoints; and the third contains the corresponding rates for each weight break. See example below.

Weight Range
(not a field in the database
just added to describe meaning
of breakpoint column) City Breakpoint Rate
0 -100 A 100 $100
101 - 200 A 200 $200
201 - 300 A 300 $300
0 -100 B 100 $100
101 - 200 B 200 $200
201 - 300 B 300 $300

I want to display the information horizontally
City 0-100 101-200 201-300
A 100 200 300
B 100 200 300

The only other twist is that different companies have different weight breaks and they are all stored in the same table. For example Company ABC's rates have the following weight breaks.

Weight Range
(not actual field
in database) Breakpoint field Rate
0-100 --> 100 $100
101-200 --> 200 $200
201-300 --> 300 $300

Company XYZ can have breakpoints such as the following.
0-50 --> 100 $100
51-100 --> 200 $200
101-150 --> 300 $300

If creating one report to accomodate both companies is not possible then I could also create seperate reports for each company. Any help or suggestions would greatly be appreciated. Thanks in advance.i would suggest that you do this in the application layer|||I agree with r937. Don't make your SQL statement too complicated, because it will be a pain in the butt to fix or change things later. It would be way easier to loop through the results and place each value in a cell in a table. By doing this you can also do some error checking for bad data before you display.

Good luck
Hope it helps|||This is essentially a "table pivoting" problem.
It's not clear from your example but I'm assuming that the number of output columns can be larger than 4, depending on the input data.
In that case, only recursive SQL (using "WITH", i.e., CTEs) will help you. (Or maybe your SQL engine has a built-in PIVOT functionality ...)

See http://tinyurl.com/6wugk for a related problem (with solution).

H.t.h.