All right, I have been trying to come up with a solution for this problem, but so far I have had no luck. Maybe someone else can point me in the right direction:
I am working with some results from surveys and each respondents has a weight value related to them for each of their responses to the questions. So if respondent #1 has a weight value of 1.5 and respondent #2 has a weight value of 2 and respondent #3 has a weight value of 1.75 and they respond to the following questions like such:
Respondet# Question Response Weight
1 Drink Light Beer Yes 1.5
1 Drink Regular Beer Yes 1.5
2 Drink Light Beer Yes 2.0
2 Drink Regular Beer No 2.0
3 Drink Light Beer No 1.75
3 Drink Regular Beer No 1.75
My over all calculation is to take the Weight value for the respondents that responded with ¡®Yes¡¯ and divide by the Weight value of all of the respondents. The gotcha is that if the respondent selected ¡®Yes¡¯ to more than one Question then I want to only include them once in the numerator.
What I am getting is: (1.5+1.5+2.0)/(1.5+2.0+1.75) = .95238
What I want to get is: (1.5+2.0)/(1.5+2.0+1.75) = .66667
As of right now I do not know how I can filter out the first respondents second response of ¡®Yes¡¯.
Right now my MDX looks like the following:
([Beverage].[Report Response Text].&[1], Measures.[Weight Value])/ iif(count(descendants(beverage.[alcohol].currentmember,,after))>0,Measures.[Weight Value]/count(descendants(beverage.[alcohol].currentmember,,after)), Measures.[Weight Value])
The denominator in this query is basically determining if there are multiple children associated with the Alcohol Type (which in this case would be Beer and the children would be Light Beer and Regular Beer). I am then taking the Weight and dividing it by the number of children associated with the Alcohol Type (which in this case would be (1.5+1.5+2.0+2.0+1.75+1.75)/2 = 5.25). The Report Response Text value of 1 = Yes and 2 = No.
Does anyone have any suggestions on how I can filter out the second ¡®Yes¡¯ response for Respondent #1