September 14, 2009 at 1:04 am
Hi All,
1.I want to find out the market share of the products relative to product group and If I select only few products, within a particular group, the market share is shown only based on the selected items and not as a percentage of the whole.(ie) Group 1 has 10 products .Product 1 has 10% market share if I choose to have all the products.But if I choose Product 1,2 & 3 alone, it shows me a higher value,since the calculation is done only on the selected values.But whatever be the number of products I had chosen to show in my report, I would like to have the value of market share pf product 1 only as 10%.Is there a way to do do that?
2.The Market Share of the product can be relative to the product group if I choose product Hierarchy.It should be relative to the Manufacturer Hierarchy, if I choose the Manufacturer dimension and within that MAnufacturer hierarchy. Is there a way to change my formula based on the currently selected dimension and hierarchy?
Thanks and Regards,
Valli
September 14, 2009 at 8:08 am
Can you post your query? It helps to see what you have already done.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 15, 2009 at 12:50 am
The following is the way I created the market share calculation
CREATE MEMBER CURRENTCUBE.[MEASURES].ValueMarketShare
AS
Case
When IsEmpty
(
[Measures].[Sales Value]
)
Then Null
When [Product Classification].[Product Classification Hierarchy].CurrentMember Is
[Product Classification].[Product Classification Hierarchy].[All]
Then 1
Else ( [Product Classification].[Product Classification Hierarchy].CurrentMember,
[Measures].[Sales Value] )
/
( [Product Classification].[Product Classification Hierarchy].CurrentMember.Parent,
[Measures].[Sales Value] )
End
September 16, 2009 at 9:20 am
Part of the answer involves using the Root() function. You can get the correct percentage by using value at the Root() as the denominator.
The second part of your query is more difficult. There is an example in MDX solutions that is similar to what you are asking, but I do not have my reference handy, so I can't give you full details. The idea is to use functions on the Axis. You would have to replace your explicit hierarchy names with a function that returned the same information.
Hope this gets you started.
September 16, 2009 at 5:12 pm
Question 1
CMIIW ...
For example: You have these members in your hierarchy
Prod A
- Prod AA
- Prod AB
* Prod ABA
* Prod ABB
When you select any product, you want to display percentage of from the total product, for example Prod ABA/Prod A or Prod AB/Prod A.
If this is the case just change your MDX query. Use the [Product Classification].[Product Classification Hierarchy].Currentmember / [Product Classification].[Product Classification Hierarchy].[All Product] instead of [Product Classification].[Product Classification Hierarchy].CurrentMember / [Product Classification].[Product Classification Hierarchy].CurrentMember.Parent
September 16, 2009 at 11:33 pm
Hi,
Thanks a lot brdudley and Hendra Eka Putra . I was able to get what I wanted using the Root function.When you get the reference for the axis function also please do post it. It will be very useful for most of my calculations.In the mean time I will also try that.Thanks a lot
Regards,
Valli
September 24, 2009 at 8:49 am
You're welcome
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply