###### Technical Articles # Average percent values on nodes

There are a lot of cases when the user have to enter some coefficients and use script logic to calculate finance figures based on this coefficients. The examples of coefficients are: prices, discount, tax percents and other. The coefficients are entered on the base member level but in reports we need to show average values of the coefficients on the node level. For example: average price per quarter, average discount per product group…

There is no simple way to achieve this goal, BPC will simply sum the base member values of the coefficients and show this sum as a node value – absolutely useless.

My proposal is to define additional member for each coefficient member and use dimension formula for the additional member to calculate coefficient from summable members.

Below you can see a simplified real life example:

In our system we are planning advertisement sales for different titles in different legal entities and of different types in a separate cube.

We have 3 types of adv sales: Display, Classified and JobOpps. For types we don’t use separate dimension, we have members dedicated for each type (with the corresponding P&L lines in the main financial planning cube).

Members of the ACCOUNT dimension:

DATA ENTRY:

PriceD
PriceC
PriceJ

PagesD
PagesC
PajesJ

*** Planned discount

DiscountD
DiscountC
DiscountJ

DEFAULT.LGF SCRIPT CALCULATED:

***Gross Sales

GrossSalesD
GrossSalesC
GrossSalesJ

``````*REC(EXPRESSION=[ACCOUNT].[PriceD]*[ACCOUNT].[PagesD],ACCOUNT=GrossSalesD)
*REC(EXPRESSION=[ACCOUNT].[PriceC]*[ACCOUNT].[PagesC],ACCOUNT=GrossSalesC)
*REC(EXPRESSION=[ACCOUNT].[PriceJ]*[ACCOUNT].[PagesJ],ACCOUNT=GrossSalesJ)``````

*** Net Sales

NetSalesD
NetSalesC
NetSalesJ

``````*REC(EXPRESSION=[ACCOUNT].[PriceD]*[ACCOUNT].[PagesD]*(1-[ACCOUNT].[DiscountD]),ACCOUNT=NetSalesD)
*REC(EXPRESSION=[ACCOUNT].[PriceC]*[ACCOUNT].[PagesC]*(1-[ACCOUNT].[DiscountC]),ACCOUNT=NetSalesC)
*REC(EXPRESSION=[ACCOUNT].[PriceJ]*[ACCOUNT].[PagesJ]*(1-[ACCOUNT].[DiscountJ]),ACCOUNT=NetSalesJ)``````

DIMENSION FORMULA MEMBERS:

*** Calculated discount to be used in reports – with average in nodes

``````DiscountCalcD = IIF([NetSalesD]=0,NULL,([GrosSalesD]-[NetSalesD])/[NetSalesD])
DiscountCalcC = IIF([NetSalesC]=0,NULL,([GrosSalesC]-[NetSalesC])/[NetSalesC])
DiscountCalcJ = IIF([NetSalesD]=0,NULL,([GrosSalesJ]-[NetSalesJ])/[NetSalesJ])``````

*** Calculated price to be used in reports – with average in nodes

``````PriceCalcD = IIF([PagesD]=0,NULL,[GrosSalesD]/[PagesD])
PriceCalcC = IIF([PagesC]=0,NULL,[GrosSalesC]/[PagesC])
PriceCalcJ = IIF([PagesJ]=0,NULL,[GrosSalesJ]/[PagesJ])``````

In the different reports we are able to see correct base and node figures (TIME nodes, TITLES nodes, ENTITY nodes) with all standard MEASURES (PERIODIC, YTD, QTD) for:

PagesD
PagesC
PajesJ

GrossSalesD
GrossSalesC
GrossSalesJ

NetSalesD
NetSalesC
NetSalesJ

DiscountCalcD
DiscountCalcC
DiscountCalcJ

PriceCalcD
PriceCalcC
PriceCalcJ