Document toolboxDocument toolbox

Calculated measures

Owned by Gvido
Last updated: Aug 05, 2022
5 min read

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