Newbie MDX question - calculated measure

Question

I'm trying to create an "adoption %" calculated measure in a cube.  I have a transaction count as a measure already (say, [Measures].[Tran Count]).

A transaction belongs to a customer, who in turn belongs to a "merchant" -- one of my dimensions.  An attribute of the merchant is CustomerCount. 

I also have a time hierarchy, and would expect transactions to occur from each customer on a monthly basis.

I'm trying to create a calculated measure that would show the adoption rate by merchant on a monthly basis.

Example: merchant A has 200 customers.  10 transactions occurred during March, 12 occurred in April (both on various days throughout the month).
The adoption rate I'm looking for is March: 5%, April: 6%.  When looking at a quarter or a year, it should probably just take the most recent adoption rate value.

I'm having trouble figuring out how to make my math apply to the month member within the time dimension, and am unsure how specifically to reference the customer count attribute (it's part of a parent-child hierarchy dimension).

Any guidance would be appreciated.  Like I said, I'm pretty new to this, but very excited about the possibilities!

NEW NOTE:  I'm trying to do this in MDX as a simple query thinking that it may help me understand how to do a Calcluated Measure.  My MDX is below (doesn't work).  I get NULL values for the Merchants with no payments during the month (expected), but for the ones WITH payments I always get 1.#INF.  I believe that means the Total Units designator below is wrong, but I can't figure out how to make it right.  I know that I have non-zero / non-null values for the Merchants in question.

Any thoughts on this now?

WITH

 

MEMBER [Measures].[Penetration] AS

([Merchant].[Parent Merchant Id].

CurrentMember, [Measures].[Payment Count])

/ ([Merchant].[Total Units])

SELECT

 

[Measures].[Penetration]

ON 0,

[Merchant].[Parent Merchant Id].[Level 03].

Members ON 1

FROM

 

[ePay Payments]

WHERE

 

([Payment Due Date].[Month].&[2009-10-01T00:00:00])

Answer 1

Hi Erik,

It seems the only problem is how to get to total count of customers for current Merchant. If so, you need to add an attribute Merchant into the dimension customer. Set correct relationship between Merchant and customer – drag Merchant below the customer. After that, you can try something like this:

{[Customer].[Merchant].currentmember*[Customer].[Customerer].members}.count

 http://technet.microsoft.com/en-us/library/ms144816.aspx

For example, the following code running on sample cube:

WITH

  MEMBER measures.[Employee %] AS

      (

        [Reseller].[Product Line].CurrentMember

       ,[Measures].[Reseller Order Count]

      )

    /

      {

        [Reseller].[Product Line].CurrentMember * [Reseller].[Reseller].MEMBERS

      }.Count

   ,Format_String = "Percent"

SELECT

  {

    [Measures].[Reseller Order Quantity]

   ,measures.[Employee %]

  } ON 0

 ,NON EMPTY

    [Reseller].[Product Line].MEMBERS * [Date].[Calendar].[Month].MEMBERS ON 1

FROM [Adventure Works]

WHERE

  [Date].[Calendar Year].&[2004];

 

If you don’t want to add attribute to the custom, you have to create a new measure for Merchant and customer. The fact table should be:

Merchant  customer       No

MerchantA  customer1     1

MerchantA  customer2     1

Merchantb  customer1     1

Create a measure with sum for No, it will return the total count of customers for current Merchant.

([Merchant].[Parent Merchant Id]. currentmember, measures.No)

 

Regards,

Raymond