I have a data cube that stores insurance policy data with a currency dimension which has four different currencies (GBP,USD,CAD,EUR). I have a distinct count Measure which counts all the policies correctly for each currency.
The problem I have is that I need to have a calculated member which shows the converted value for each currency i.e. GBPconvert (which shows all policies as their GBP value). Therefore the calculated member is currently:-
[CURRENCY].[SCC].USD / 1.49 +
[CURRENCY].[SCC].CAD / 2.24 +
[CURRENCY].[SCC].EUR / 1.58) "
and is attached to the Currency Dimension.
This works fine for all numeric value conversions i.e. policy premium; however the policy count measure also gets affected by this and hence the policy count gets reduced according to the above formula, which is incorrect as this should remain unaffected. Is there anyway I can exclude the Policy Count measure from this calculation but apply it to all other Measures?
Or is there another way I can approach this?