Hi,
how can write MDX in calculation for this case I highlighted in red?
product Sale contribution
-
a | 30.00 30/80 = 0.375
b | 50.00 50/80 =0.625
total | 80.00
-
[measures].[sale]/ sum ([measures].[sale] in current level ) ?
Thanks in advance.
Dear Friend,
try this and tell me if works:
Code Snippet
WITH
SET [InterestingProduts] AS
'{[Product].&[2],
[Product].&[1]}'
MEMBER [Product].[TotalOfInterestingProducts] AS
'SUM([InterestingProduts])'
MEMBER [MEASURES].[Percentage] AS
'[Measures].[Sales]/([Measures].[Sales],[Product].[TotalOfInterestingProducts])',
SOLVE_ORDER=5
SELECT {[Measures].[Sales],[MEASURES].[Percentage]} ON COLUMNS,
{[Product].members,[Product].[TotalOfInterestingProducts]} ON ROWS
FROM [YourCube]
Is more completed... but try if works!
regards!
|||Hi PedroCGD,
Thanks for your code
It works perfectly.
And I have another challenge that if I want to view percentage in each level (please see the example below) then how should I define for this scnario?
Currently, it only works in product a / b level. If I drill down and see the percentage then it calculates like this below:
i.g. product a-1 : 10/100 --> want to correct 10/30
product a-2: 20/ 100 > want to correct 20/30
percentage
product a
product a-1 10 10/30
product a-2 20 20/30
sub total 30 30/100
product b
product b-1 20 20/70
product b-2 50 50/70
sub total 70 70/100
Total 100
Here is the calculation script below:
CREATE MEMBER CURRENTCUBE.[Product].[Div-Quad Hierarchy].[Total] AS 'SUM([ALL])'; /* I may change this but not clear now */
CREATE MEMBER CURRENTCUBE.[MEASURES].[% Contrib Sls $]
AS
'[Measures].[Net Sale Dollars_Prod]/([Measures].[Net Sale Dollars_Prod], [product].[Div-Quad Hierarchy].[Total])',
SOLVE_ORDER=2,
FORMAT_STRING = "Percent",
VISIBLE = 1 ;
Please give me any comments.
Thanks in advance 
|||
Dear abc,
I'm happy you get it!! :-)
I will try to help you in your new requirement more late (few hours), ok? At the moment I'm very, very busy! :-(
Regards!!!
|||Hi Pedro,
It seems you are busy
If I run the mdx query,I can get a right result for each product level if I change this area that I highlighted in yellow.
But if I add this member in the calculation script then I don't know how to apply this for each particular product level.
FYI, I have product hierarchy (division > Q > Q name > ...) and we want to see the contribution percent in the total amount of the each product level not grand total of total products.
MEMBER [Product].[Div-Quad Hierarchy].[Total] AS
'SUM([Product].[Div-Quad Hierarchy].[division].&[interesting product name ])'
MEMBER [MEASURES].[Percentage] AS
'[Measures].[Net Purch Dollars_prod]/([Measures].[Net Purch Dollars_prod], [product].[Div-Quad Hierarchy].[Total])',
SOLVE_ORDER=2,
FORMAT_STRING="percent"
Please give me some comments.
Thanks in advance.
