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.