You can use dimension calculation if you have a calculation that will be used in all the cubes that contain the dimension.

Dimension calculation can be done at leaf level or consolidated level.

To create a dimension calculation for leaf level, double click on dimension, click 52 icon on leaf level column.

106

It will open up a new window, you have option to type directly to Expression box or to use available operators to build simple calculations in Simple tab or to use functions from Functions tab. In this example we are going to use Simple tab as per below.

After the expression is automatically created, drag and drop members of dimension on top of <operand> in the expression box.

107

The result:

108

Do the same steps for Commissionable  Sales Revenue

109

Now the Unit sale price and Commissionable Sales Revenue are calculated. However the consolidated level for Unit sale price, Exchange Rate, and Price_USD need to be adjusted. For this example we are going to fix Exchange Rate and Unit sale price.

110

For Exchange Rate, we will use Force to Zero function because total of exchange rate is a meaningless number. For Unit sale price we will use Weighted Average.

From Aggregation tab select Weighted Average.

111

Drag and drop to Expression box and click Apply.

112

For Exchange Rate, from Aggregation tab select Force to Zero and click Apply.

113

The result:

114

For full list of functions available in dimension calculation click here.

Leave a Reply

Your email address will not be published. Required fields are marked *