SQL Server Performance Forum – Threads Archive
Simple Question – Creating Calculated Member for %
Hi, As the users drill down one of the dimensions, I want to show the breakup of each sub member in terms of %age e.g. Suppose total Sales are 300 and breakup is as follows:Category A = $ 120
Category B = $ 65
Category C = $ 115
I want that when the user drills down to the 2nd level in the Products dimension (let’s say this Level is called Category) then I want the %age of Sales for each category – all adding up to 100 So this is what I should get (for the desired % breakup) Category A = 40
Category B = 22
Category C = 38
I’ll appreciate your help in making such a Calculated field. Many TIA.
I just tested this out real quick on the Foodmart sales cube and it worked. You just have to modify my code to your liking, create a Calculated Member called "Customer Sales %" then set it to Percent format. [Measures].[Unit Sales]/([Measures].[Unit Sales], Ancestor([Customers].CurrentMember, [Customers].[(All)])) But if you want to project the percentage while drilling from any dimension you can use this "Sales %" [Measures].[Unit Sales]/([Measures].[Unit Sales], [Customers].[All Customers],[Education Level].[All Education Level],[Gender].[All Gender]) I just added 3 Dimensions as an example Hope you get the idea ! Raulie
Hewlett-Packard Company
Hi Raulie, Thanks for the reply and i think you read my mind i.e. Yes I would like to use the solution while I am drilling in any dimension and at any level. I kind of got the idea and tried your solution but am not getting the results. I just get a 1 in some cases and nothing as I go to a different level or a different dimension… ??? I know that I am doing something wrong somewhere…. Using your solution as the basis I changed it to include ALL of my dimensions… BTW, once I get things working, I am assuming that your solution will work regardless of whichever level (in any dimension) I am at?
The reason it read one at the grand total for the level is because you need to format the data type to use percentage under the advanced properties tab, and yes the solution I gave you will work for any level you are drilling down to. Use my second solution instead of the first one since you want to drill to all dimensions.
[Measures].[Unit Sales]/([Measures].[Unit Sales], [Customers].[All Customers],[Education Level].[All Education Level],[Gender].[All Gender])
Raulie
Hewlett-Packard Company
Will give it another try and post back. Thanks Raulie. Appreciate your reply and help.
]]>