Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Joozh
Posting Yak Master
145 Posts |
 Posted - 2004-12-20 : 04:52:58
|
| 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 = $ 120Category B = $ 65Category C = $ 115I 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 100So this is what I should get (for the desired % breakup)Category A = 40Category B = 22Category C = 38I'll appreciate your help in making such a Calculated field.Many TIA.Thanks & Regards.-J |
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-12-20 : 05:33:06
|
| You can try with a Calculated Member similar to the following:Sum({[Category].CurrentMember},[Measures].[Sales])/Sum({[Category].[All Category]},[Measures].[Sales])*100Remember to use the correct names ... esp. for [All Category] if you have changed the All Caption.Multiplication by 100 can be removed if you format the member as a 'Percent'.Let me know if this worked for u.Jojo |
 |
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-12-20 : 06:22:55
|
| Thanks jojo. I am having a little problem putting this to use and can you kindly help here... I have a number of Dimensions. I'll explain the setup of just two of these and maybe this will clarify things at my side and will allow you to help me further:I have a Product Dimension and a Time Dimnesion. Now to make the required Calculated column for % generic (so that it works in both the Dimensions), I tried to write the MDX code as is but did not work... Any clues? |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-12-21 : 00:12:24
|
| What exactly the problem is? Any error? Or, the results are wrong? I use this logic (successfully, that is) in many of my cubes with lots of dimensions.As you can see, I am using the CurrentMember of Category and so it works irrespective of where the dimension is selected ... row, column or page.I dunno how this can be made generic ... you have to specify the dimension on which you need the percentage. I think, you need to create Calculated Members for all the dimensions, if that is what u need.Jojo |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-12-21 : 03:10:25
|
| Hi,Thanks for the reply.Let me try to disect it step by step and hopefully get thesolution working. Well my ideal requirement is to be able to use a solution which will work for all dimensions and regardless of which level I am at. However that's for a later discussion and for now let me try to understand your solution:I have a Product Dimension and while I am in the Calculated Member Builder. I see a level by the name of (All)....I don;t know if my question is clear but can you help me re-write the MDX code assuming that all I had was a Product Dimension.Thanks. |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-12-21 : 03:47:35
|
| Ok ... I understand what you are saying. Expand '(All)' and you will see the 'All Product'. Select that. This is the caption of the 'All' level. If you go to Product dimension editor, select Product (the top most level) and go to Properties - Advanced tab. You should have 'All level' enabled. And, you can see the caption for it. This is the caption that we should use in the Calculation. Actually, you should be able to use the index (Zero for All level), but my experiment failed with that. So, I always use the All Caption to have a valid Calculated Member.Hope this helps.Jojo |
|
|
|
|
|
|
|
|
Simple Question - Creating Calculated Member for %