# Calculated measures

## 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 flex.bi 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

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

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.

## Time difference calculations

flex.bi 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 flex.bi 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 flex.bi 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 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 flex.bi 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 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'
)
)
```

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

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`