Update – 30 May 2009

Thanks to a comment by Mathias (and followup email – thanks dude) the following code as a calculated measure in the OLAP cube did exactly what I needed:

([Measures].[Costsum],[KeepDim1].currentmember,[KeepDim2].currentmember,[LoseDim1].currentmember,[LodeDim2].defaultmember)

/

([Measures].[QtyAVG],[KeepDim1].currentmember,[KeepDim2].currentmember,[LoseDim1].currentmember,[LodeDim2].defaultmember)

The trick was you have to specifically exclude all dimensions you dont want included in the calc usinf defaultmember otherwise the calc will use currentmember for it.

—End update

So im struggling with MDX and SAS at the mo.

I have a cube that has a number of dimension, for example:

• Product
• Customer
• Period
• Location
• Sales Person

And I have some measures

• Cost
• Quantitiy

Now I want to calculate unit cost (i.e Cost / Quantitiy) but (and here is my problem) I only ever want to calculate it based on a combination of the Product and Sales Person dimensions.

So if only Product and Sales Person are added to the cube view (in WRS) then I can just divide Cost by Quantity.

But as soon as I add Organisation (or any other dimension), the cost gets split by the multiple organisation dimension members and therefore the dynamic calculation doesnt work, so I need to hardcode the formula to only pick up the cost based soley on the Product and Sales Person dimensions.

Any suggestions (using custom measures in OLAP cubes, or custom data item in Information Maps, im easy)?