Ranking by One Field, Displaying Multiple Fields

I need to create MDX for the following logic: After ranking Lenders by Loan Amount and displaying loan count (see below), calculate the following add'l displayed fields. Dollars Behind 1st, % of Dollars Behind 1st, and Count Behind First (which will sometimes be a negative number, since lender with highest amounts may not have highest count).

How do I build the MDX expression (query ) to support the last three calc'd fields

Spreadsheet Example Logic:

Lender Loan Amts Count $ Behind 1^st % (of $) Behind 1^st Count Behind 1^st

A $100,000,000 100 n/a n/a n/a

B $ 70,000,000 120 $30,000,000 30% (20)

C $ 60,000,000 50 $40,000,000 50% 50

----------------------------------------------------------------------------------------------------------------

Thank you in advance for help.

Daniel Upton

Here's a sample Adventure Works query, using Sales Amount and Order Quantity measures:

Thank you, Deepak. Here is how it looks right now in my cube.

The ORDER fn works for me, but the three calc'd fields are returning errors, and I cannot find a code discrepancy:

CODE ADAPTED TO OUR CUBE:

set [LenderRankByLoanVolume] as

Order([Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity]

- {

[Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity].[Miscellaneous]

},

[Measures].[Loan Amount], DESC)

Member [Measures].[Behind 1st $] as

iif (

[Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity].CurrentMember

IS [LenderRankByLoanVolume].Item(0), Null,

( [Measures].[Loan Amount],

[LenderRankByLoanVolume].Item(0)

)

- [Measures].[Loan Amount]

),

FORMAT_STRING = 'Currency'

Member [Measures].[Behind 1st %] as

[Measures].[Behind 1st $] /

([Measures].[Loan Amount],

[LenderRankByLoanVolume].Item(0)),

FORMAT_STRING = 'Percent'

Member [Measures].[Behind 1st Count] as

iif (

[Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity].CurrentMember

is [LenderRankByLoanVolume].Item(0), Null,

( [Measures].[Loan Count],

[LenderRankByLoanVolume].Item(0)

)

- [Measures].[Loan Count]

),

FORMAT_STRING = '#,#;(#,#)'

{[Measures].[Loan Amount],

[Measures].[Loan Count],

[Measures].[Behind 1st $],

[Measures].[Behind 1st %],

[Measures].[Behind 1st Count]} ON 0,

[LenderRankByLoanVolume] ON 1

FROM [Sales_Loans_Cube]

-----------------------------------------------------------

Returns Results:

Do you see any code discrepancy Would additional info or code help

Returns Messages:

Yes, that creates a very cool MDX query. Thank you, Deepak. Sadly, our otherwise-slick OLAP front-end product (Strategy Companion) does not accept MDX Queries directly (only MDX Expressions). So (and I'm kind of begging now)...

How can this query be converted into either...

(a) A series of separate MDX expressions, or...

(b) something like an "MDX shell" dimension, (perhaps with a single attribute and with expression implemented as a member of that attribute.

Here is what I've produced so far...

CREATE SET CURRENTCUBE.[Lender Rank By Volume]

AS Order([Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity]

- {

[Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity].[Miscellaneous]

},

[Measures].[Loan Amount], DESC) ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Behind 1st $]

AS iif([Lender Name].[Master - DBA - Deed Stamp].CurrentMember

IS [Lender Rank By Volume].Item(0), Null,

( [Measures].[Loan Amount],

[LenderRankByVolume].Item(0)

)

- [Measures].[Loan Amount]

),

FORMAT_STRING = "$#,##0",

VISIBLE = 1;

(I've not created the other two members yet, since the above one throws an error).

When browsed in OWC, it disallows me from dropping the set as a column in OWC browser, and returns an error of #VALUE! for the "Behind 1st $" member.

What's wrong here