calculating percentage in MDX

0
votes

I'm trying to write a mdx query that would calculate the income percentage for each product in 3 different columns, depending on the percentage "base" - the Group (that the product is in) income, the subgroup income and total income. So far this is my code

    with 
member [Measures].[Percent] as
([Measures].[Income]) /
([Products].[product].currentmember.parent,
[Measures].[Income])
,format_string = "percent"

select 
    {
        [Measures].[Percent]
    }
    on columns,
    {
        [Products].[Product].[Product]
    }
    on rows
from [CUBE]

It calulates the percentage based on the total income, but I have no idea how to change the code to fill the criteria I mentioned earlier. I tried rearrenging the code many times in many different ways, (trying to calculate the sobgroup percentage example)

 with 
member [Measures].[Percent] as
([Measures].[Income], [Products].[Subgroup]) /
([Products].[product].currentmember.parent,
[Measures].[Income])
,format_string = "percent"

etc but I only get the same result or errors. I'm still very new to mdx so any help or tip would be much appreciated.

1
mdxpercentage
Do you have a product - subgroup hierarchy in place? – SouravA
The hierarchy is Group - Subgroup - Product – some
ok but there will be a user hierarchy with the levels in it AND there will be seperate attribute hierarchies for each of the levels in the user hierarchy. Can you add a link to a picture of the hierarchy structure? – whytheq
Thank you for your time, I hope this is what you've asked for PICTURE – some
your user hierarchy looks like it is called Hierarchy ?! You will need to use this hierarchy as your original script was hitting the attribute hierarchy called Towar (with the 6 little squares next to it) I changed my script. – whytheq

1 Answers

0
votes

I'm still a little unsure of the requirements but you can add an extra parent if you like:

WITH 
MEMBER [Measures].[Percent_ofSubCategory] as
   DIVIDE(
     [Measures].[Income]
    ,([Measures].[Income], [Towary].[Hierarchy].currentmember.parent)
   )
,format_string = "percent"
MEMBER [Measures].[Percent_ofCategory] as
   DIVIDE(
     [Measures].[Income]
    ,([Measures].[Income], [Towary].[Hierarchy].currentmember.parent.parent)
   )
,format_string = "percent"
MEMBER [Measures].[Percent_ofAll] as
   DIVIDE(
     [Measures].[Income]
    ,([Measures].[Income], [Towary].[Hierarchy].[All])
   )
,format_string = "percent"
SELECT 
    {
      [Measures].[Percent_ofSubCategory]
     ,[Measures].[Percent_ofCategory]
     ,[Measures].[Percent_ofAll]
    }
    on 0,
    [Towary].[Hierarchy].[Towar].[Towar].members
    on 1
from [CUBE];