# Calculated members

If you have not yet read them then please start with the main eazyBI concepts and create reports tutorial. This tutorial will explain how to define new calculated members using MDX calculation formulas.

Each cube **dimension** contains one or more **hierarchies** with one or more hierarchy **levels**. Each hierarchy level contains dimension **members**. There are *stored* members that imported from source files or applications. Additionally, you can define **calculated members **using calculation formulas. The calculations will run during each report execution and be available alongside the stored members. Calculation formulas are defined using the MDX query language.

There is a special dimension **Measures** which contains all imported **measures **and** ****properties**. Calculated members defined in the **Measures **dimension typically are called **calculated measures**.

Please watch **demo videos** in this documentation page to see in action typical definitions and usage of calculated members.

**On this page:**

## Define new calculated member

If you would like to define new calculated member then expand corresponding dimension (e.g. *Measures*) and expand **Calculated members **section:

If you would like to define a new calculated member then click on the **Define new** link. If you would like to edit (or delete) an existing calculated member then click on the **edit** link. If you do not have rights to edit calculated members in the current account then you will see just the **show** link which will show the definition of an existing calculated member. When you define a new calculated member or edit an existing calculated member you will see the following calculated member definition form:

Each calculated member should have a name (unique within dimension) and a calculation formula (the rest of the tutorial will teach how to write calculation formulas). In addition, you can specify how calculated member value should be formatted (e.g. as an integer, decimal, date or using default formatting). From the right sidebar you can quickly select other members, dimensions, operators or functions to insert them in the calculation formula.

After defining a calculated member formula press **Update** to save it or **Delete** to delete an existing calculated member or **Cancel** to discard any changes. If a calculated member formula will be invalid then a corresponding error message will be displayed.

**Do not modify or delete pre-defined calculated members that were created by source application import** – their original definition will be recreated next time when you will perform the source application import. If you need to create a modified version of an existing pre-defined calculated member then copy its calculation formula and create a new calculated member with a different name and modify copied calculation formula for the new calculated member.

Let’s start writing some calculation formulas! Following examples will use Sales demo cube dimensions and measures to illustrate creation of calculation formulas.

## Dimension, hierarchy, level and member names

When writing calculation formulas you will need to reference dimensions, dimension hierarchies, hierarchy levels and other existing dimension members. In MDX query language all names are enclosed in square brackets `[]`

.

To reference a **dimension** you just enclose its name in square brackets, e.g. `[Customers]`

, `[Time]`

or `[Measures]`

.

If dimension has just one **hierarchy** then you can reference the primary hierarchy in the same way as dimension, e.g. `[Customers]`

or `[Measures]`

. When you import **Time** dimension then it automatically will create a main hierarchy (with Year, Quarter, Month and Day levels) as well as a **Weekly** hierarchy (with Year, Week and Day levels). `[Time]`

will reference **Time** dimension main hierarchy but `[Time.Weekly]`

will reference **Time** dimension **Weekly** hierarchy.

When you expand specific dimension and expand **All hierarchy level members** then you see the names of all main **hierarchy levels**. You can reference particular hierarchy level with `[dimension or hierarchy name].[level name]`

. E.g. `[Customers].[City]`

references **City **level of the main hierarchy of the **Customers** dimension.

In case of the Time dimension use `[Time].[Year]`

, `[Time].[Quarter]`

, `[Time].[Month]`

, `[Time].[Day]`

to reference the main hierarchy levels and `[Time.Weekly].[Year]`

, `[Time.Weekly].[Week]`

, `[Time.Weekly].[Day]`

to reference the Weekly hierarchy levels.

Each dimension typically will have a default **All member** which can be used to get totals of measures by this dimension. If **Customers **dimension has a default

**All**member named

**All Customers**then you can reference it with

`[Customers].[All Customers]`

. As default **All**member can be renamed it is safer to use

`[Customers].DefaultMember`

to get the same result.In case of the **Time** dimension there are two default **All** members for each hierarchy – `[Time].DefaultMember`

and `[Time.Weekly].DefaultMember`

. In calculation formulas you can use expression `[Time].CurrentHierarchy.DefaultMember`

to get either `[Time].DefaultMember`

or `[Time.Weekly].DefaultMember`

depending on which **Time** hierarchy is used in a report.

When you want to reference **top level dimension members** then you use `[dimension or hierarchy name].[member name]`

. E.g. `[Customers].[USA]`

will reference **USA** member from the top level **Country** level. To reference **detailed level dimension members **you need to specify full “hierarchy path” to this member, e.g.* *`[Customers].[USA].[CA]`

** **to reference a

**member in the**

*CA***State Province**level under a

**USA**parent member. Or use

`[Customers].[USA].[CA].[San Francisco]`

to reference a city and so on.All **measures** are in the top level of **Measures** dimension and you can reference them with as `[Measures].[Store Sales]`

, `[Measures].[Store Cost]`

etc.

## Simple arithmetic calculations

Now that you know how to reference other existing measures and other dimension members we can create a simple arithmetic calculations.

If we have `[Measures].[Store Sales]`

and `[Measures].[Store Cost]`

measures then we can define a new calculated measure `Profit`

(which will have full name `[Measures].[Profit]`

) with a formula:

[Measures].[Store Sales] - [Measures].[Store Cost]

When you will use this new measure Profit in your eazyBI reports then for any combination with other dimension values this formula will calculate a difference between a **Store Sales** measure value and a **Store Cost** measure value.

You can use defined calculated measures also in other calculated measures that you define later. For example, you can now define `[Measures].[Margin %]`

with a formula:

[Measures].[Profit] / [Measures].[Store Sales]

and it will calculate margin as a number from 0 to 1. If you would like to display results as a percentage value then change **Formatting** of this calculated measure to use an **integer** or **decimal percentage** formatting.

You can perform arithmetic calculations also for calculated members in the other dimension. For example, you can define a `[Customers].[West coast]`

calculated member in the **Customers** dimension with a formula:

[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA]

and now when you will combine in reports `[Customers].[West coast]`

with `[Measures].[Store Sales]`

you will get a total sales for all these three states together. If you will combine it with `[Measures].[Profit]`

you will get a total profit for all these three states.

There is a default hidden `[Measures].[Fact Count]`

measure which will return number of fact rows in a cube database table. You can use it to calculate simple average values, for example, define `[Measures].[Average Store Sales]`

as

[Measures].[Store Sales] / [Measures].[Fact Count]

## Tuples

When you use `[Measures].[Store Sales]`

in a formula then it will calculate **Store Sales** measure value for the **current context** of each report cell. Current context includes corresponding row and column dimension members of the report cell as well as selected page dimension members.

If you would like to override in a measure value calculation some current context dimension member with a different member then you need to use a **tuple** of this measure and other dimension member (or several dimension members). MDX syntax for tuples is `(member_1, member_2, ..., member_n)`

. For example, if you would like to get **Store Sales** measure value for all customers then in a formula you should use a tuple:

( [Measures].[Store Sales], [Customers].DefaultMember )

This formula means that **Store Sales** should be calculated using all dimension members from the current context except **Customers** dimension for which default member (**All Customers**) should be used.

Tuples are frequently used to calculate percentage of a measure value from some total value. Let’s define a calculated measure `[Measures].[Sales / customers total %]`

with a formula (and percentage formatting)

[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].DefaultMember)

When in report you will combine `[Measures].[Sales / customers total %]`

with, for example, `[Customers].[USA].[CA]`

then you will see percentage of California sales from the total customer sales.

Maybe you don’t want to see a percentage from the total sales but a percentage from customer hierarchy direct parent sales (e.g. for City level show percentage from corresponding State sales). In this case you can use the `CurrentMember`

dimension or hierarchy property to access the current context dimension member. For example, `[Customers].CurrentMember`

will return the current member in the **Customers** dimension for which the formula is evaluated. Or in other words if **Customers** dimension is placed on report rows then by using `[Customers].CurrentMember`

we know for which row the formula is evaluated.

If we have the current member then we can navigate to other dimension members relative from this member. `[Customers].CurrentMember.Parent`

will return parent member in **Customers** hierarchy for the current member. `Ancestor([Customers].CurrentMember, [Customers].[Country])`

will move from current member up to “ancestor” in **Country** level.

So we can define a calculated measure `[Measures].[Sales / parent customer %]`

with a formula:

[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].CurrentMember.Parent)

Please see also DefaultContext function documentation which can be used to override the context for evaluation of measures.

## Moving in time

As you can move to a different dimension hierarchy levels you can also move to a different dimension members in the same dimension. It is typically used in the **Time** dimension when you want to compare measures between different time periods or aggregate a time period range.

For example, a calculated measure `[Measures].[Sales monthly growth]`

with a formula:

[Measures].[Store Sales] - ([Measures].[Store Sales], [Time].CurrentMember.PrevMember)

will calculate a `[Measures].[Store Sales]`

growth comparing to the previous **Time** dimension member period. If this calculated measure will be combined in a report with a month in the **Time** dimension then it will show a growth comparing to the previous month. If it will be combined with a year then it will show the growth comparing to the previous year.

There are several functions that will help you “to move in time”:

`[Time].CurrentMember.PrevMember`

returns the previous member, which contains data, in the same hierarchy level (it will return an empty member for the first member)`[Time].CurrentMember.NextMember`

will return the next member`[Time].CurrentMember.Lag(2)`

will return the previous member with a distance 2 (use any number for the argument,`Lag(1)`

is the same as`PrevMember`

)`[Time].CurrentMember.Lead(2)`

will return the next member with a distance 2 (`Lead(1)`

is the same as`NextMember`

)`ParallelPeriod([Time].[Year], 1, [Time].CurrentMember)`

will return the “parallel” Time member one year ago (e.g. for the Day level member`Jan 01 2012`

it will be the day`Jan 01 2011`

but for the Month level member`Jan 2012`

it will be the month`Jan 2011`

)`OpeningPeriod([Time].[Day], [Time].CurrentMember)`

will return the first descendant of the current**Time**member at the**Day**level (the first day of a year, a quarter or a month which is defined in the**Time**dimension)`ClosingPeriod([Time].[Day], [Time].CurrentMember)`

will return the last descendant of the current**Time**member at the**Day**level (the last day of a year, a quarter or a month which is defined in the**Time**dimension)

If cube has only one Time dimension then the `[Time].CurrentMember`

argument can be omitted as it will be used by default. So you can use shorter expressions `ParallelPeriod([Time].[Year], 1)`

**, **`OpeningPeriod([Time].[Day])`

, `ClosingPeriod([Time].[Day])`

**.**

Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations). Then you can use `.Name`

member property to get a member name as a calculation formula result. For example, define calculated measure `[Measures].[test opening day]`

with a formula:

OpeningPeriod([Time].[Day], [Time].CurrentMember).Name

and use it in report together with the **Time** dimension members and see if you get the expected result for the **Time** dimension members at different levels.

## Sets

Now you know how to navigate to individual dimension members. But quite often you would like to perform operations on a **set of dimension members.** For example, you would like to get a sum or an average value of some measure over a selected set of dimension members.

The easiest way how to use sets in calculation formulas is to use list of members enclosed in curly braces, e.g.

{[Customers].[USA].[CA], [Customers].[USA].[OR], [Customers].[USA].[WA]} {[Time].[2011], [Time].[2012]}

If you would like to select a set as a range of sequential dimension level members then you can specify the first and the last member and use `:`

between them. For example, this will create a set of dates from `Jan 01 2012`

to `Jan 15 2012`

:

[Time].[2012].[Q1 2012].[Jan 2012].[Jan 01 2012]:[Time].[2012].[Q1 2012].[Jan 2012].[Jan 15 2012]

Quite often you don’t want to specify the exact range of members but would like to get all dimension hierarchy level members. You can do it with `Members`

method. This will get all months in the **Time** dimension:

[Time].[Month].Members

When you want to test which members will be returned by some set expression then you can use the `SetToStr`

function to create a string of concatenated member full names. For example, define a calculated measure `[Measures].[test all months]`

with a formula:

SetToStr([Time].[Month].Members)

There are several other useful functions for working with sets. Here are some examples how to use them:

`[Customers].[USA].[CA].Children`

returns a set of children members using a dimension hierarchy (in this example all cities in California)`Descendants([Customers].[USA], [Customers].[City])`

returns set of member descendants at the specified hierarchy level (in this example all cities in USA)`[Customers].[USA].[CA].[San Francisco].Siblings`

returns all members which have the same parent as this member (in this example all cities in California), it is the same as using`[Customers].[USA].[CA].[San Francisco].Parent.Children`

There are additional methods that you can use to get just the first or the last member of these sets – `FirstChild`

, `LastChild`

, `FirstSibling`

, `LastSibling`

.

## Sets of time periods

There are additional useful set selection methods for **Time** periods

`PeriodsToDate([Time].[Year],`

returns all periods from the beginning of the current member in the specified level (in this example from the beginning of the current year) until the specified member.`[Time]`

.CurrentMember)`YTD([Time].CurrentMember)`

is a shorter version of the same function (an abbreviation from Year-to-date).Note that even though Weekly hierarchy also has Year level, this function will only work in default Time hierarchy. For weekly hierarchy use the

`PeriodsToDate([Time.Weekly].[Year])`

.`QTD([Time].CurrentMember)`

returns a quarter-to-date set of members`MTD([Time].CurrentMember)`

returns a month-to-date set of members`WTD([Time.Weekly].CurrentMember)`

returns a week-to-date set of members

As mentioned earlier If you have just one **Time** dimension in your cube then you can use even shorter expressions `YTD()`

, `QTD()`

,** **`MTD()`

and `WTD()`

as by default they will receive as an argument the current member of the time dimension – `[Time].CurrentMember`

. But sometimes you need to pass a different argument to these functions. For example, if you would like to compare current year-to-date aggregates with year-to-date aggregates a year ago then you can use the following expression to get a year-to-date set for the corresponding **Time** dimension member a year ago:

YTD(ParallelPeriod([Time].[Year], 1, [Time].CurrentMember))

And if you like shorter expression then you can omit the current member argument here as well – `YTD(ParallelPeriod([Time].[Year], 1))`

. Most of the time related functions will use `[Time].CurrentMember`

as a default member argument.

If you want to get a number of last periods from the **Time** dimension then you can use the `LastPeriods`

function. For example:

LastPeriods(3, [Time].CurrentMember)

will return a set with the current **Time** member and the two previous members. If you would like to get the current and the next two **Time** periods then use negative value `-3`

instead of `3`

. And you can omit the last argument for this function as well and use `LastPeriods(3)`

.

When you want to get the current **Time** dimension member in the **Weekly** hierarchy then you need to use `[Time.Weekly].CurrentMember`

member expression. Sometimes you need to write a formula which should use either `[Time].CurrentMember`

(when creating e.g. monthly report) or `[Time.Weekly].CurrentMember`

(when creating weekly report). In this case you can use

[Time].CurrentHierarchyMember

which will return either the **Time** main hierarchy or the **Weekly** hierarchy current member depending on which **Time** hierarchy you use in your report.

## Set operations

There are several operations that you can perform on sets:

`{set1, set2, ..., setn}`

returns a union of two or more sets`Except(set1, set2)`

returns*set1*members but removes any members that are in*set2*`Head(set, number)`

returns a set with the first*number*of members from the original set (if*number*is not specified then a set from the first set member is returned)`Tail(set, number)`

returns the last*number*of members from a set`set.Item(position)`

returns one member from a set with the specified*position*(starting from zero). So if you would like to get the first member of a set you can use the expression`Head(set).Item(0)`

Quite frequently you would like to filter set members using some condition. You can do this with `Filter(set, condition)`

. For example, in this way you can filter all cities with sales larger than 1000:

Filter([Customers].[City].Members, [Measures].[Store Sales] > 1000)

Within the condition expression `[Customers].CurrentMember`

references the current set member for which the condition is evaluated. For example, this will return all cities which name starts with *San* (using *MATCHES* operator with regular expression)

Filter([Customers].[City].Members, [Customers].CurrentMember.Name MATCHES 'San .*')

Other typical function that is used in conditions is `IsEmpty`

. This expression will return all cities which have non-empty sales amount:

Filter( [Customers].[City].Members, NOT IsEmpty([Measures].[Store Sales]) )

Previously a simple set to string function `SetToStr`

was mentioned that is useful for expression testing purposes. But if you would like to format set results in a customized way then you can use `Generate(set, string_expression, separator_string)`

. For example, the following expression will return city names concatenated using comma where there is no sales amount recorded:

Generate( Filter( [Customers].[City].Members, IsEmpty([Measures].[Store Sales]) ), [Customers].CurrentMember.Name, ', ' )

## Aggregates

Now that you know how to select different sets of dimension members you can calculate different aggregated values from these sets:

`Sum(set, numeric_expression)`

calculates*numeric_expression*for each*set*member and returns a sum of all these results. For example,`Sum(LastPeriods(3), [Measures].[Store Sales])`

will calculate the total sales for the last three**Time**periods starting from the current**Time**dimension member.`Count(set)`

returns a count of set members.`Count(set, ExcludeEmpty)`

will return a count of set members for which corresponding measure values are not empty.`Avg(set, numeric_expression)`

calculates the average of*numeric_expression*in*set*`Max(set, numeric_expression)`

returns the maximum value of*numeric_expression*in*set*`Min(set, numeric_expression)`

returns the minimum value of*numeric_expression*in*set*`Median(set, numeric_expression)`

returns the median value of*numeric_expression*in*set*Time difference calculations

eazyBI defines an additional function `DateDiffDays(from_date, to_date)`

which will return a difference in days between two dates. It can be used together with `Now()`

function (which returns the current time) to get a distance in days between selected Time dimension member and the current date, for example

DateDiffDays([Time].CurrentMember.StartDate, Now())

In addition eazyBI defines a function `DateAddDays(date, number_of_days)`

which will return a new date in the past (if *number_of_days* is negative) or in the future (if *number_of_days* is positive). For example, this will return a date which is 5 days from the current **Time** dimension member date.

DateAddDays([Time].CurrentMember.StartDate, 5)

Sometimes it is useful to get the **Time** dimension member which corresponds to the actual current date. This can be done with eazyBI specific dimension hierarchy level property `CurrentDateMember`

. For example, these expressions will return the members for the month of the current date and the week of current date:

[Time].[Month].CurrentDateMember [Time.Weekly].[Week].CurrentDateMember

If there is no **Time** dimension level member that corresponds to the current date (e.g. if data for current month or week are not yet imported) then `CurrentDateMember`

will return the last period before the current date which is present in the **Time** dimension corresponding level.

There is also an additional eazyBI specific dimension hierarchy level method `DateMember`

for which you can provide a dynamic date expression argument and get the corresponding member (or the last period before that date which is present in the **Time** dimension). For example:

[Time].[Day].DateMember('7 days ago')

If you want to select a subset of Time dimension level members between specified dates then you can use `DateBetween`

function to filter level members using a date range expressions. For example, the following expression will return set of last 7 days from `[Time].[Day]`

level members:

Filter( [Time].[Day].Members, DateBetween([Time].CurrentMember.StartDate,'7 days ago','today') )

## Aggregate members in other dimensions

In the beginning of this tutorial we defined a `[Customers].[West coast]`

calculated member in the **Customers** dimension with a formula:

[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA]

But it would be better if we could expand or drill into `West coast`

when using it in reports. To enable that you need to define calculated member using `Aggregate(set)`

function. When you will combine such calculated member in a report together with other measure then you will get an aggregated result (by default a sum) of this measure over the specified set of members. So if you will define a `[Customers].[West coast]`

calculated member with a formula

Aggregate({ [Customers].[USA].[CA], [Customers].[USA].[OR], [Customers].[USA].[WA] })

then you will get the same result as previously but, in addition, you will be able to expand or drill into `West coast`

member and see the detailed results for `CA`

, `OR,`

and `WA`

.

Members used in `Aggregate`

function will appear in the report in the same order as they are included in the function, therefore, it could be used to rearrange the order of the dimension members in report rows.

In a similar way, you can aggregate more complex set expressions. For example, you could define `[Customers].[US without west coast]`

** **calculated member with a formula:

Aggregate( Except( [Customers].[USA].Members, { [Customers].[USA].[CA], [Customers].[USA].[OR], [Customers].[USA].[WA] }) )

which will calculate the aggregate of all US states without west coast states and you can drill into these states as well.

## Conditions

You can write formulas with conditions using `IIF`

and `CASE`

functions. For simple IF / THEN / ELSE type of conditions use `IIF(condition, if_true_expression, if_false_expression)`

(notice that there are two `I`

letters in `IIF`

), for example

IIF([Measures].[Profit] > 0, 'Profit', 'Loss')

If there are many conditions then it is easier to use a `CASE`

function, for example

CASE WHEN [Measures].[Profit] > 1000 THEN 'Big profit' WHEN [Measures].[Profit] > 0 THEN 'Small profit' ELSE 'Loss' END

If all conditions are comparisons of the same expression to different expected values then the other `CASE`

form can be used, for example

CASE [Time].CurrentMember.Level.Name WHEN 'Month' THEN Sum(LastPeriods(3), [Measures].[Store Sales]) WHEN 'Day' THEN Sum(LastPeriods(90), [Measures].[Store Sales]) END

In `IIF`

and `CASE`

conditions standard comparison operators can be used (`=`

, `<`

, `<=`

, `<>`

, `>`

, `>=`

) as well as `AND`

, `OR`

and `NOT`

operators as well as several specific operators:

`IS`

returns whether two objects are the same, for example,`[Customers].CurrentMember IS [Customers].DefaultMember`

(which will be true if**Customers**current member is the default**All Customers**member)`IN`

and`NOT IN`

returns whether a member is in a set, for example`[Customers].CurrentMember IN [Customers].[USA].[CA].Children`

## Member properties

Dimension members has some default properties (like `.Name`

and `.Key`

) as well as they can have additional custom properties. eazyBI source application import (e.g. from Basecamp, Highrise or Jira) are also importing additional dimension fields from source systems. MDX has standard `Properties`

function to access member properties. eazyBI defines an additional `get`

(and also with a longer name `getProperty`

) function which will return an empty result instead of an exception if no property is defined for the current dimension level.

For example, Jira import adds the **Created at** property for all imported issues. The following expression returns the **Created at** property value for the **Issue** dimension current member:

[Issue].CurrentMember.get('Created at')

## Date type conversion

Sometimes you might need to convert string expression to an integer, a decimal or a date expression (for example, to convert Highrise custom field string value to corresponding type to be able to use it in further calculations). There are several functions available for data type conversions:

`CInt(value)`

returns a value converted to an integer`CDbl(value)`

returns a value converted to a*double floating number*type (should be used when results should be decimal)eazyBI defines additional function

`DateParse(value)`

which will try to convert value to a date value using different date formats (for example both`2012-01-31`

and`Jan 31 2012`

will be converted to the correct date). There is a MDX standard`CDate`

function but it supports less date formats.`DateParse`

function also supports dynamic date expressions as offset from today (learn more about them in date filters help page). For example, you can use`DateParse('today')`

or`DateParse('30 days ago')`

or`DateParse('1 week from now')`

.

## Default values

Sometimes you want to return a default value if some measure or function will return an empty value. In these cases you can use `CoalesceEmpty(expression, default_value)`

function, for example:

CoalesceEmpty([Measures].[Store Sales], 0)

## Comments

It is possible to write comments in calculation formulas. Use comments either to describe some non-obvious complex calculations or also commenting is valuable during debugging of the calculation formulas. When something is not working as expected then comment all formula lines and leave uncommented just some part of the formula that you would like to debug.

-- one line comment expression -- comment until end of line /* multi line comment */

## Special comments with annotations

Available on eazyBI Cloud or starting from version 4.0 of eazyBI add-on for Jira or Private eazyBI.

You can add special comments in the formulas with annotations for the calculated member. These annotations are used in the eazyBI user interface to group calculated members or to allow or disable certain actions:

`-- annotations.group=...`

Put the calculated member in the specified group in the **Calculated members** section of the dimension in the report builder.

`-- annotations.disable_drill_into=true`

Do not allow the **Drill into** action for this calculated member.

`-- annotations.disable_drill_across=true`

Do not allow the **Drill across** action for this calculated member.

`-- annotations.disable_drill_through=true`

Do not allow the **Drill through** action for this calculated member.

`-- annotations.disable_drill=true`

Disable all drill actions for this calculated member.

## List of all MDX functions

This tutorial covered most frequently used MDX functions and examples how to use them. If you didn’t find what you need then take a look at the list of all MDX functions.

If you have any unclear questions or issues when writing calculation formulas then please contact eazyBI support.