Projected Margin % using Global variables & UDDC

Modified on Tue, 5 Mar at 6:58 PM

Projected Margin % using Global variables & UDDC

Usage of global variable in custom measures saves users from the tedious task of modifying various expressions and filter formula manually and provides simple “what if” analysis scenarios. Since the Global variables are created at the dataset level, they can be accessed globally with various expressions.

Note: Global variables created for one dataset cannot be accessed from within objects created from another dataset.

Example 1:

Let's see how to calculate the Projected Margin based on the Cost of goods percentage change.

The below Margin cross-tab shows the Gross sales, Cost of Goods and Margin percentage ( a UDDC derived as “((GrossSales-CostofGoods)/GrossSales)*100 ” ) of various product categories for the year 2014.

SALES MARGIN CROSS-TAB

Now, to create Projected Margin based on Cost of goods, you need to create a global variable 'COGS_Perc_Change', and use it in expression for creating UDDC for Projected Margin.

Step 1

In the Cross-tab Toolbar, click Manage global variables. The system displays the Manage global variables dialog box.

Step 2

In the Manage global variables dialog box, click the Add icon. The system displays the Add Global Variable dialog box.

ADD GLOBAL VARIABLE DIALOG

Step 3

In the Name field, enter the name as 'COGS_Perc_Change'.

Step 4

Since the variable values are not associated with any dimension, you do not have to check the checkbox Associate with dimension.

Enter the value as '5' in the default value text-box and click OK to save the variable.

Once Global variable is created, you need to create a Custom Measure Column (UDDC) 'Projected Margin'. This UDDC should be calculated on the basis of the variable COGS_Perc_Change, Cost of Goods and Gross Sales columns.

Step 5

In the Sales cross-tab, right-click on the measure cell. The system displays the menu.

Step 6

In the menu, select Add Column. The system displays Add custom measure (UDDC) dialog box.

ADD CUSTOM MEASURE (UDDC) DIALOG

Step 7

Enter the name as 'ProjectedMargin' and create the expression in the Expression box as '((GrossSales-(CostofGoods*(1+($COGS_Perc_Change$/100))))/GrossSales)*100 '. Global variable can be used with $ sign within UDDC expression.

Step 8

The VERIFY EXPRESSION will verify the expression and display a message if the expression is valid or not. Click OK.

The sales margin cross-tab will be displayed with the Projected Margin values. The Projected Margin values will be based on the global variable value. Default value of global variable is 5, so expression will be - (((GrossSales-(CostofGoods*(1+(5/100))))/GrossSales)*100).

SALES MARGIN CROSS-TAB WITH PROJECTED MARGIN COLUMN

You can change the value of COGS_Perc_Change to see different projections of Margin. Any change in COGS_Perc_Change would be reflected in all cross-tab where the value of COGS_Perc_Change is used.

The list of global variables created through Manage global variables can be accessed from the Global

variables option in the Cross-tab toolbar.

Below given are some scenarios of various projections of the Margin when there is a change in the Cost of Goods percentage.

Scenario 1: When there is a discount of 3% applied to the Cost of Goods.

GLOBAL VARIABLES DIALOG FOR CHANGING THE VALUE

PROJECTED MARGIN AFTER APPLYING THE DISCOUNT

Scenario 2: When there is an increase of 10% in the Cost of Goods.

GLOBAL VARIABLE WINDOW FOR CHANGING THE VALUE

PROJECTED MARGIN AFTER INCREASING THE COST

Note: This article is based on Smarten Version 5.x. This may or may not be relevant to the Smarten version you may be using.

custom measure Global variable User Defined Data Column UDDC what if scenarios Manage global variables Expression Business Intelligence Support Business Intelligence Articles Business Intelligence Training