Calculated Measure in Analysis Services

Question

The AdventureWorksDW has the construct of the Financial Reporting Fact table. I have a similar fact table where the fact contains only the FK to the dimension tables and a value. The measure gets it's context from an DimAccount dimension. Are there any code samples that show how to do a simple ratio in a calculated member between two measures of the AdventureWorks Financial Reporting sample?

So basically I would like to see say Total Long term Debt / Total Assets from AdventureWorksDW? What I need is the expression or MDX.

Thanks in advance.

FrankPl FrankPl · Accepted Answer · 2013-08-21T20:15:34

Use a query like this:

with member [Account].[Accounts].[Balance Sheet].[Dept by Assets] as
            IIf([Account].[Accounts].[Assets] <> 0,
                [Account].[Accounts].[Long Term Liabilities] / [Account].[Accounts].[Assets],
                null
                )
             ,format_string = "0.00%"
select {
       [Account].[Accounts].[Assets],
       [Account].[Accounts].[Long Term Liabilities],
       [Account].[Accounts].[Dept by Assets]
       }
       on columns,
       { [Measures].[Amount] }
       on rows
  from [Adventure Works]

You can define members in any hierarchy, not only in the measures. In the definition, you should use the parent member before the name of the new member, to tell AS the position in the hierarchy. This is more important for CREATE MEMBER in the cube calculation script than for WITH MEMBER, as it influences the position where the client tool will display it.

Calculated Measure in Analysis Services

1 Answers