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

Combinations of Other Measures