Creating a calculated member is a great way to add functionality to your cube. Most times calculated members are based entirely off values in your fact table, but every so often you need to pull a figure from a dimension. One such example would be if you have an exchange rate that is based on a location and is not time dependent.
To use this value inside a calculated member, your calculation needs to leverage the ‘key’ property of your dimension. In the example below, the dimension is called ‘Location’ and has an attribute called ‘Exchange Rate’. The ‘Amount Base’ is the value we want to adjust from the fact table:
[Measures].[Amount Base]*[Location].[Exchange Rate].currentmember.properties("key")
The end result in the cube is as follows:
The problem with this, is that as we are dealing with a calculated member, the amounts do not roll-up or aggregate to the parent levels. The calculation of the member needs to change to take this into account using a small amount of recursion:
IIF( IsLeaf([Location].[Exchange Rate].CurrentMember), [Measures].[Amount Base]*[Location].[Exchange Rate].currentmember.properties("key"), Sum([Location].[Exchange Rate].Children, [Measures].[Amount]) )
This will in turn give you the desired results: