I have multiple dimensions which require members that return a % of total result. An example of one of these dimensions is below, the bold members are the ones that I am trying to create. The idea is that i can then filter this dimension by any of my measures.
Sex Dimension:
All Participants
Male
Female
% Male
% Female
I have tried a number of methods to get this to work. Here is what I have attempted:
Dimension Member with Scoped / Direct Assignments
I manually create a member in the dimension table called % Female and assign the scoped assignment listed below. This produces the correct result in the Sex dimension against any measure accurately and I can filter it without error. The problem is when i review any other dimension the results are fragmented and displayed in decimals even when they normally don't.
SCOPE([Sex].[Sex].[% Female]);
THIS = Divide([Sex].[Sex].[Female],[Sex].[Sex].[All Customers)]);
FORMAT_STRING(THIS)="Percent";
END SCOPE;
Calculated Member with Scoped / Direct Assignment
When I create a calculated member, I see none of the symptoms listed above but I am unable to filter it. Whenever I try to filter it I get an error which states: "A set has been encountered which can not contain calculated members."
CREATE MEMBER CURRENTCUBE.[Sex].[Sex].[All Customers].[% Male]
AS null;
[Sex].[Sex].[All Customers].[% Male] = Divide([Sex].[Sex].[Male], [Sex].[Sex].[All Customers], 0);
Is there any way that I could put these calculated members into a single dimension?
What are the other methods that you guys use to tackle problems like this?
Thank you
