SSAS Calculation & MDX

0
votes

I cannot figure out how to turn this SSAS Calculation expression into a MDX query so I can evaluate the parts of the calculation. Please help!

The calculation is applied to a cube with dimensions: Year, Month, Store, Department, and Account. It also has 1 measure named Amount.

The calculation expression is as follows:

(
   ([Accounts].[All Levels].[Level 1].&[Gross Profit].&[Dept1],[Measures].[Amount])+
   ([Accounts].[All Levels].[Level 1].&[Gross Profit].&[Dept2],[Measures].[Amount])
)
/
(
   ([Accounts].[All Levels].[Level 1].&[Labor],[Measures].[Amount])+
   ([Accounts].[All Levels].[Level 1].&[Expenses],[Measures].[Amount])
)

It's basically trying to take the Gross Profits for Departments 1 & 2 only, and then divide it by the total of the Labor and Expenses (all departments).

This will be done for the year, month, and store selected (or in total).

Now, what I want to do is see what is making up the final results I'm seeing by the different dimensions in the cube so I can validate the calculation.

For example, let's say the resulting amount is 40% if I select Year: 2018, Month: Jan: Store: L001.

What I'd like to see what the amounts were for Gross Profit (for Dept1 and Dept2) versus the amounts for Labor and Expenses. Meaning the actual underlying amounts, but in detail

I've tried just placing in the calculation expression:

select
    (
       ([Accounts].[All Levels].[Level 1].&[Gross Profit].&[Dept1],[Measures].[Amount])+
       ([Accounts].[All Levels].[Level 1].&[Gross Profit].&[Dept2],[Measures].[Amount])
    )
    /
    (
       ([Accounts].[All Levels].[Level 1].&[Labor],[Measures].[Amount])+
       ([Accounts].[All Levels].[Level 1].&[Expenses],[Measures].[Amount])
    ) 
FROM 
    My Cube
;

And got the error: "Parser: The statement dialect could not be resolved due to ambiguity."

1
expressionssasmdx

1 Answers

2
votes

Try this:

WITH MEMBER [Measures].[Gross Profit] as
    (
       ([Accounts].[All Levels].[Level 1].&[Gross Profit].&[Dept1],[Measures].[Amount])+
       ([Accounts].[All Levels].[Level 1].&[Gross Profit].&[Dept2],[Measures].[Amount])
    )
    /
    (
       ([Accounts].[All Levels].[Level 1].&[Labor],[Measures].[Amount])+
       ([Accounts].[All Levels].[Level 1].&[Expenses],[Measures].[Amount])
    )
SELECT [Measures].[Gross Profit] on COLUMNS
FROM 
    My Cube
;