Page Comparison

On this page:

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 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.

...

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.

...

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.

Image RemovedImage Added

Time difference calculations

eazyBI 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 eazyBI 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 eazyBI 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 eazyBI 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:

...