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.


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.


The result:


Do the same steps for Commissionable  Sales Revenue


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.


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.


Drag and drop to Expression box and click Apply.


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


The result:


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 *