Working with Calculated Members

[colinmrice]

Hi,

I'm new to this forum and looking for some help defining a calculated member (of even if that is the way I should be doing it).

I'm looking for some help with building a formula in a Calculated Member in my cube. I have the dimensions and fact entries defined in my cube, and the data is displayed correctly in the MDX query test.

What I need to do is create a Calculated Member that will return the gross sales based on a calculation using my existing dimensions. I'm new to MDX and formulas so just looking for some pointers. Please see the attached file that hopefully explains what I'm trying to do.

 

Thanks in advance!

Colin

[colinmrice]

Code:

    <CalculatedMember name="Transactions TransType1" formula="COALESCEEMPTY((Measures.[Total TransactionAmount], [TransactionType].[TransactionType].[TransID].[1]),    Measures.[Total TransactionAmount])" dimension="Measures" visible="true">

    </CalculatedMember>



    <CalculatedMember name="Transactions TransType2" formula="COALESCEEMPTY((Measures.[Total TransactionAmount], [TransactionType].[TransactionType].[TransID].[2]),    Measures.[Total TransactionAmount])" dimension="Measures" visible="true">

    </CalculatedMember>



    <CalculatedMember name="Transactions TransType6" formula="COALESCEEMPTY((Measures.[Total TransactionAmount], [TransactionType].[TransactionType].[TransID].[6]),    Measures.[Total TransactionAmount])" dimension="Measures" visible="true">

    </CalculatedMember>



    <CalculatedMember name="Transactions TransType97" formula="COALESCEEMPTY((Measures.[Total TransactionAmount], [TransactionType].[TransactionType].[TransID].[97]),    Measures.[Total TransactionAmount])" dimension="Measures" visible="true">

    </CalculatedMember>



    <CalculatedMember name="Gross Sales GameType1" formula="([Measures].[Transactions TransType1] + [Measures].[Transactions TransType2] + [Measures].[Transactions TransType6] - [Measures].[Transactions TransType97])" dimension="Measures" visible="true">

   </CalculatedMember>



    <CalculatedMember name="Gross Sales GameType2" formula="([Measures].[Transactions TransType1] + [Measures].[Transactions TransType2])" dimension="Measures" visible="true">

   </CalculatedMember>



    <CalculatedMember name="Gross Sales GameType3" formula="([Measures].[Transactions TransType1] + [Measures].[Transactions TransType2])" dimension="Measures" visible="true">

   </CalculatedMember>