September 23, 2015 at 9:45 am
Hello everyone,
I need to calculate the percentage of amount per 2 dimensions (country,brand) out of the total amount for all the countries and their brands , i'm using the current MDX expression :
([dim_country].[hierarchy_Zone_setofcountry_country],[dim_branch].[hierarchy_branch_brand],[Measures].[Amount])/sum([dim_country].[hierarchy_Zone_setofcountry_country].[All],[Measures].[Amount])
the values i'm getting are not what i want, for exemple :
i have two countries : Norway (amount=30, that contains 2 brands: céline=10, dior=20) , sweden ( amount 40, one brand =40)
Now what i wana get is :
Norway :
- Céline amount=10, Value of % that i want = (10/70)%
- Dior amount=20, Value of % that i want = (20/70)%
Total for Norway : 30, Value of % that i want = (30/70)%
Sweden :
- Céline 0 | -
- Dior 40 | (40/70)%
Total for Sweden 40 (40/70)%
Grand total 70 100%
Can someone please help me with the correct mdx expression to get the output that i want.
for more information please check the 2 screenshots attached
Thank you.
September 23, 2015 at 10:01 pm
It looks like your calculation is based on the total for the product in all countries instead of being the total for all products in all countries.
Try this instead:
([dim_country].[hierarchy_Zone_setofcountry_country], [dim_branch].[hierarchy_branch_brand], [Measures].[Amount])/sum([dim_country].[hierarchy_Zone_setofcountry_country].[All], [dim_branch].[hierarchy_branch_brand].[All], [Measures].[Amount])
September 28, 2015 at 7:37 am
Yes i tried the expression that you suggested before, but then i got an error saying : too much arguments for the function "SUM", the maximum number of agruments for this function is 2.
September 28, 2015 at 8:39 am
i found the right expression , it should be more like :
([dim_country].[hierarchy_Zone_setofcountry_country], [dim_branch].[hierarchy_branch_brand], [Measures].[Amount])/sum({[dim_country].[hierarchy_Zone_setofcountry_country].[All].children}, ([dim_branch].[hierarchy_branch_brand].[All], [Measures].[Amount]))
regards.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply