calculation question

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.