The top 5 functions used when building apps
 |
Calculation measures include simple math and functions. Functions are conditional logic, lookups and many other powerful pre-built functions to manage and leverage multidimensionality and build complex relationships. |
Things to remember when building calculation measures
Building calculation measures is virtually identical to the process in spreadsheets. There are a few differences.
- Cell references are replaced by measure names (native multidimensional power takes care of positional references automatically).
- Often the native multidimensional power will remove the need for calculations (e.g. use summarize measure for sum methods, min/max, count and averages).
- Table views are ideal for organizing and displaying measures in various layouts (e.g. a summary and detailed table view), removing need for aggregation measures.
- You can combine functions in a calculation measure.
IF (conditional logic)
Use for comparison and logic checks between measures such as greater than, less than or equal to. IF follows the classic "IF THEN ELSE" format bur no THEN or ELSE required.
Consider LOOKUP when using drivers and assumptions.
When using equal to use "==" (two x equal sign).
In the example, confirm if the employees cumulative earnings have exceeded the threshold for a particular employer tax.
Measure:
Federal unemployment earnings = IF((YTD personnel < Federal tax limit), YTD personnel, Federal tax limit)
This measure = IF((measure A < measure B), outcome if true, outcome if not true)
Nest as many IF as needed.
If using AND the format for the calculation measure is:
This measure =IF(AND(measure A < measure b, measure C == measure c), outcome if both true, outcome if both not true)
CUMULATE
Cumulate values using a forward/backward looking measure. Use for Year to Date and moving averages.
In the example, cumulate the employees cost over time to report year to date cost each month.
Measure:
YTD direct personnel = CUMULATE(Month personnel, Time.Month, Calendar.Month reference)
This measure = CUMULATE (measure to be cumulated, based on this dimension, using this measure value for number of cumulations)
LOOKUPEQ
Use to lookup measures based on a dimension measure. Perfect for updating drivers and assumptions.
In the example, lookup the travel allowance for an employee based on their office location.
Measure:
Travel allowance = LOOKUPEQ(Office, Travel allowance by office, List of offices)
This measure = LOOKUPEQ(this table measure dimension, target table measure, target table dimension matching this table measure dimension)
SUMBY
Used to consolidate detailed measures while aggregating their details across detailed dimensions.
Consider using table views if objective is to display aggregations.
The Expenses table is summarizing from various detailed tables such as People costs (detailing employees costs and attributes), Capital (asset purchases and depreciation) and Travel (air, hotel and other related expense) and where those tables have differing dimensionality. Consolidate into a summary table with consistent dimensions for a total view analysis.
Measure:
6000 Salaries and wages = SUMBY(Salary for role, List of employees, List of roles, List of offices).
This measure = SUMBY(measure to be consolidated, summarize dimension A, summarize dimension B, summarize dimension C)
Summarize by as many dimensions as needed.
BROADCASTCOPY
Takes a high level measure and broadcast to details. Useful for referring to high level (e.g. year) drivers when needed in lower level (e.g. months) calculation measures.
The employee costs include annual drivers such as Federal tax limit and the Federal tax rate. These drivers are entered into a People drivers table and need to be referred to as each employee's cost is calculated.
Measure:
Federal tax limit = BROADCASTCOPY(Annual people drivers.Federal tax limit for year, Time.Month)
This measure = BROADCASTCOPY(measure to be broadcast, dimension basis of broadcast)
 |
 |