title: SSAS Cube - Calculated Member from a Dimension
body: <p>I have a very simple cube.</p>
  
  <p>This is the schema of the data source:</p>
  
  <pre><code>FactSales:
    - DimRateCardID int (FK to DimRateCard)
    - SalesDate int (FK to DimDateID)
    - Sales int
  
  DimRateCard
    - DimRateCardID int
    - Product Name string
    - Customer Name string
    - Rate money
  
  DimDate:
    - DimDateID int
    - Year int
    - Month int
    - Day int
    - IsHoliday bit
  </code></pre>
  
  <p>The cube structure is</p>
  
  <pre><code>Measure Groups:
   - FactSales.Sales 
   - DimRateCard.Rate
  
  Dimensions:
      1) DimRateCard:
        - Attribute - DimRateCardID, Product Name, Customer Name,
        - Hierarchy - (Customer Name, Product Name)
  
      2) DimDate:
        - Attribute - DimDateID, Year, Month, Day, IsHoliday
        - Hierarchy - (Year, Month, Day)
  </code></pre>
  
  <p>I am trying to create a calculated member to calculate the total cost as follows</p>
  
  <pre><code>WITH 
  MEMBER Measures.[Total Cost]
      AS [DimRateCard].[Rate].membervalue * [FactSales].[Sales]
  FORMAT_STRING = "Currency",
  SELECT {
  Measures.Cost,
  [DimRateCard.Rate]
  } ON COLUMNS,
  [DimRateCard].[Customer].Members
  ON ROWS
  FROM [DW Snow Flake]
  </code></pre>
  
  <p>The Cost column returns #error, but seems to work fine if I select [DimRateCard].[RateCardID] on COLUMNS. I come from a background of relationship database. This is something that I am trying to achieve in SQL.</p>
  
  <pre><code>SELECT drc.CustomerName, drc.ProductName, SUM(f.Sales * drc.Rate)
  FROM FactSales f
  INNER JOIN DimRateCard drc ON drc.DimRateCardID = f.DimRateCardID
  GROUP BY drc.CustomerName, drc.ProductName
  </code></pre>
  
  <p>Can someone shed some light on this issue?</p>
  
  <p>Just to clarify why I put the rate in a dimension rather in FactSales. The way the rate is determined is by the number of sales made over a date range (as determined by marketing). For example, say we charge $5 per unit for purchase of 5 units or less, $4 units for purchase between 6 to 15 units, $3 for purchase between 16 to 25 units. If you buy 3 units today, 5 tomorrow, and 10 the week after (18 in total), you get charged $3 per unit.</p>
  
  <p>Thanks</p>