MDX Recipes for Calculated Measures
This section describes how to create MDX expressions for some commonly needed calculated measures:
Combinations of Other Measures
For a calculated measure, the value expression often has the form of a mathematical formula that combines measure expressions. For example:
(MEASURES.[measure A] + MEASURES.[measure B]) * 100
Or:
(MEASURES.[measure A] + MEASURES.[measure B])/MEASURES.[measure C]
More formally, in this expression, you can use the following elements:
-
References to measures.
-
Numeric literals. For example: 37
-
Percentage literals. For example: 10%
There must be no space between the number and the percent sign.
-
Mathematical operators. InterSystems IRIS Business Intelligence supports the standard mathematical operators: + (addition), - (subtraction), / (division), and * (multiplication). It also supports the standard unary operators: + (positive) and - (negative).
You can also use parentheses to control precedence.
For example: MEASURES.[%COUNT] / 100
-
MDX functions that return numeric values, such as AVG, MAX, COUNT, and others.
In addition to the functions already discussed, Business Intelligence supports several scalar functions: SQRT, LOG, and POWER.
Tip:
The MDX function IIF is often useful in such expressions. It evaluates a condition and returns one of two values, depending on the condition. You can use this to avoid dividing by zero, for example.
Percentages of Aggregate Values
It is often necessary to calculate percentages of the total record count or percentages of other aggregate values. In such cases, you can use the %MDX function, which is an InterSystems extension. This function executes an MDX query, which should return a single value, and returns that value, which is unaffected by the context in which you execute the function. This means that you can calculate percentages with measures defined by value expressions like this:
100 * MEASURES.[measure A] / %MDX("SELECT FROM mycube")
For example:
WITH MEMBER MEASURES.PercentOfAll AS '100 * MEASURES.[%COUNT]/%MDX("SELECT FROM demomdx")'
SELECT MEASURES.PercentOfAll ON 0, diagd.MEMBERS ON 1 FROM demomdx
PercentOfAll
1 None 84.56
2 asthma 6.85
3 CHD 3.18
4 diabetes 4.89
5 osteoporosis 2.21
Distinct Member Count
In some cases, for a given cell, you want to count the number of distinct members of some particular level. For example, the DocD dimension includes the level Doctor. We could count the number of unique doctors who are primary care physicians for any given set of patients. To do so, we define a calculated measure that uses the following value_expression:
COUNT([docd].[h1].[doctor].MEMBERS,EXCLUDEEMPTY)
We can use this measure in a query as follows:
WITH MEMBER MEASURES.[distinct doctor count] AS 'COUNT(docd.doctor.MEMBERS,EXCLUDEEMPTY)'
SELECT MEASURES.[distinct doctor count] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
distinct doctor co
1 0 to 9 38
2 10 to 19 38
3 20 to 29 38
4 30 to 39 40
5 40 to 49 41
6 50 to 59 40
7 60 to 69 33
8 70 to 79 31
9 80+ 28
Semi-Additive Measures
A semi-additive measure is a measure that is aggregated across most but not all dimensions. For example, customers’ bank balances cannot be added across time, because a bank balance is a snapshot in time. To create such measures, you can use the %LAST function, an InterSystems extension to MDX.
Consider the following measures:
-
Balance is based on the source property CurrentBalance and is aggregated by summing.
You would avoid aggregating this measure over time, because it would give incorrect results; that is, you should use this measure only in pivot tables that include a time level for rows or columns.
-
Transactions is based on the source property TxCount and is aggregated by summing.
You can define a calculated measure called LastBalance and use the following value_expression:
%LAST(Date.Day.Members,Measures.Balance)
The %LAST function returns the last non-missing value for a measure evaluated for each member of the given set. In this case, it finds the last day that has a value and returns that value.
Filtered Measures (Tuple Measures)
A normal measure considers all records in the fact table for which the source value is not null. In some cases, you may want to define a filtered measure, which has the following behavior:
For a filtered measure (also informally called a tuple measure), use a value_expression like the following:
([MEASURES].[my measure],[DIMD].[HIER].[LEVEL].[member name])
In this case, value_expression is a tuple expression where:
For example, the Avg Test Score measure is the average test score considering all patients who have a non-null value for the test. Suppose that in addition to the Avg Test Score measure, your customers would like to see another column that just shows the average test scores for patients with coronary heart disease (the CHD diagnosis). That is, the customers would like to have the measure Avg Test Score - CHD. In this case, you can create a calculated measure that has the following value_expression:
(MEASURES.[avg test score],diagd.h1.diagnoses.chd)
For example:
WITH MEMBER MEASURES.[avg test score - chd] AS
'(MEASURES.[avg test score],diagd.h1.diagnoses.chd)'
SELECT MEASURES.[avg test score - chd] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
avg test score - c
1 0 to 9 *
2 10 to 19 *
3 20 to 29 *
4 30 to 39 *
5 40 to 49 78.00
6 50 to 59 75.75
7 60 to 69 80.71
8 70 to 79 83.33
9 80+ 55.25
Measures for Another Time Period
It is often useful to view the value of a given measure for an earlier time period, while viewing a later time period. As an example, you can define a calculated measure called UnitsSoldPreviousPeriod and use the following value_expression:
([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])
Because of how this measure is defined, it is meaningful only if you use the DateOfSale dimension on the other axis of the query. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],[MEASURES].[UNITSSOLDPREVIOUSPERIOD]} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold DateOfSale
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
...
Notice that the caption of the second column is based on the dimension used within the value expression, rather than the name of the calculated member that we defined. We can use the %LABEL function to provide a more suitable caption. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],%LABEL([MEASURES].[UNITSSOLDPREVIOUSPERIOD],"Units (Prv Pd)","")} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold Units (Prv Pd)
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
6 Jun-2009 17 22
7 Jul-2009 24 17
8 Aug-2009 30 24
...
These examples use a time-based level, because this kind of analysis is common for time levels. You can, however, use the same technique for data levels.
Measures That Refer to Other Cells
It is often useful to refer to the value in a different cell of the pivot table. To do so, you can use the %CELL and %CELLZERO functions. Each of these functions returns the value of another cell of the pivot table, by position. If the given call has no value, %CELL returns null; in contrast, %CELLZERO returns zero.
These functions have many uses. For one example, you can use %CELL to calculate a running total (in this case, the cumulative inches of rainfall):
SELECT {MEASURES.[Rainfall Inches],%CELL(-1,0)+%CELL(0,-1)} ON 0, {dated.year.1960:1970} ON 1 FROM cityrainfall
Rainfall Inches Expression
1 1960 177.83 177.83
2 1961 173.42 351.25
3 1962 168.11 519.36
4 1963 188.30 707.66
5 1964 167.58 875.24
6 1965 175.23 1,050.47
7 1966 182.50 1,232.97
8 1967 154.44 1,387.41
9 1968 163.97 1,551.38
10 1969 184.84 1,736.22
11 1970 178.31 1,914.53
