Creating On-the-Fly Calculated Members in MDX Forms

MDX Forms support the creation of calculated members directly in the MDX query using the WITH MEMBER clause. This means you can create derived calculations (e.g. revenue estimates, variances, ratios) without modifying the TM1 model — the calculations exist only in the report.

Prerequisites

A working MDXForm3 sheet with query switching configured (see Stacking Dimensions and Switching MDX Queries)

Step 1 – Create a Copy of the Sheet

Duplicate MDXForm3 and rename the copy MDXForm4.

Step 2 – Add a Row for the On-the-Fly Calculation Option

Insert a new row above row 6. In cell A6, type:

Cell B6 will hold the dynamic CONCATENATE formula that builds the MDX query (built in Step 6).

Step 3 – Identify the Measures to Use

In the Listing Analysis MSolution dimension, the following measures are available for this exercise:

Measure	Description
`price_avg`	Average listing price
`minimum_nights`	Minimum number of nights required per booking
`availability_365`	Number of days available per year

Note: there are no pre-existing revenue measures in the model — these will be created as on-the-fly calculated members.

Step 4 – Build the Calculated Member Lookup Table

In cells C11:E12, create a reference table that maps each calculated member to its input measures:

Column C	Column D	Column E
`MinRevenue`	`price_avg`	`minimum_nights`
`EstRevenue`	`price_avg`	`availability_365`

MinRevenue = average price × minimum nights (minimum revenue per booking)
EstRevenue = average price × days available per year (estimated annual revenue)

Step 5 – Add a Dropdown to Select the Calculated Member

In cell B20, create a data validation dropdown list with the two options:

MinRevenue
EstRevenue

Step 6 – Build the Dynamic MDX Query with CONCATENATE

Because the final MDX query exceeds Excel's 255-character cell limit for a plain string, use the CONCATENATE function to assemble it dynamically. Paste the following formula into cell B6:

How This Formula Works

Part	Purpose
`WITH MEMBER ... As (...)`	Defines the calculated member inline in the query
`$B$20`	The selected calculated member name (MinRevenue or EstRevenue)
`VLOOKUP(..., 2, FALSE)`	Looks up the first factor (e.g. `price_avg`) from the reference table
`VLOOKUP(..., 3, FALSE)`	Looks up the second factor (e.g. `minimum_nights` or `availability_365`)
`TM1SubsetToSet(...)`	Returns a predefined TM1 subset of measures for the columns
`TopCount(..., 10, ...)`	Returns the top 10 listings ranked by average price

Step 7 – Validate the MDX Query First (Optional but Recommended)

Before wiring up the CONCATENATE formula, test the raw MDX query in the Slice MDX Lab. The following is the base query for MinRevenue:

Confirm the query returns expected results before proceeding.

Result

The completed MDXForm4 sheet allows users to:

Select a calculated member (MinRevenue or EstRevenue) from the B20 dropdown.
Click Rebuild Sheet (ALT+F9).
See a live Top 10 Listings report with the selected on-the-fly calculated measure appended to the standard columns — all without any changes to the TM1 model.

Key Takeaway

The WITH MEMBER clause in MDX, combined with Excel's CONCATENATE function and data validation dropdowns, gives MDX Forms a level of flexibility that is not possible with traditional Active Forms.