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]
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.
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
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.
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:
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' ) )
You can write formulas with conditions using
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
CASE conditions standard comparison operators can be used (
>=) as well as
NOT operators as well as several specific operators:
ISreturns whether two objects are the same, for example,
[Customers].CurrentMember IS [Customers].DefaultMember
NOT INreturns whether a member is in a set, for example
[Customers].CurrentMember IN [Customers].[USA].[CA].Children