#
Calculated measures

**On this page:**

## Simple arithmetic calculations

Now that you know how to reference other existing measures and other dimension members we can create 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 the 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 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 a 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

A **tuple** is a collection of member(s) with the restriction that no two members can be from the same dimension. A tuple uniquely identifies a slice of data from a cube and it represents a single data cell if all dimensions are represented.

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 another 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 the percentage of a measured value from some total value. Let’s define a calculated measure `[Measures].[Sales / customers total %]`

with formula (and percentage formatting)

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

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

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

then you will see the 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.

## Aggregate measures

You can calculate different aggregated values, like, sum, average, median, count, minimal and maximal values, over the set of dimension members.

`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. There is an example of how Sum() could be used.`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. There is an example of how Count() could be used.`Avg(set, numeric_expression)`

calculates the average of*numeric_expression*in*set*. There is an example of how Avg() could be used.`Max(set, numeric_expression)`

returns the maximum value of*numeric_expression*in*set*. There is an example of how Max() could be used.`Min(set, numeric_expression)`

returns the minimum value of*numeric_expression*in*set*. There is an example of how Min() could be used.`Median(set, numeric_expression)`

returns the median value of*numeric_expression*in*set*.

Before you create the calculated measure to get the Average, Median, Min, or Max value, check whether you could use standard functionality **Add calculated → Statistical → Average/Median/Min/Max **to show those values in the report. For more details on this functionality see documentation on Add standard calculations based on a selected measure.

For more details on how to construct calculated measure with sets check the training video below:

For more details on how to construct calculated measure with sets check the training video below:

Slides: 23-handling-sets.pdf

## Time difference calculations

eazyBI defines an additional function DateDiffDays() that will return a difference in days between two dates. It can be used together with Now() a function (which returns the current time) to get a distance in days between the selected Time dimension member and the current date, for example

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

In addition, eazyBI defines a function DateAddDays() 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 that 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 the 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 the current month or week are not yet imported) then CurrentDateMember returns 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 the DateBetween() function to filter level members using 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') )

## Conditions

You can write formulas with conditions using Iif() and CASE statement 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`

approach 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`