Calculated Members and Measures
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
WITH MEMBER — Query-Scoped Calculated Members
Description: Defines a calculated member that exists only within the scope of the current query. Most commonly used to create new measures (in the Measures dimension).
Syntax:
WITH MEMBER Parent.MemberName AS Expression
[, MEMBER Parent.MemberName AS Expression ...] SELECT ...Input: Parent dimension path, member name, MDX expression Output: The calculated member is available on query axes
Calculated Measure — Profit Margin
Business Use Case: Calculate profit margin percentage to evaluate product profitability without storing a derived measure in the model.
WITH MEMBER [Measures].[Profit Margin] AS
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount] SELECT
{[Measures].[Internet Sales Amount], [Measures].[Total Product Cost], [Measures].[Profit Margin]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]Expected Output:
| Internet Sales Amount | Total Product Cost | Profit Margin |
Bikes | $28,318,144 | $19,877,786 | 29.8% |
Accessories | $700,759 | $404,011 | 42.3% |
Year-over-Year Growth
Business Use Case: Compare current year sales to prior year to identify growth trends.
WITH MEMBER [Measures].[Prior Year Sales] AS ([Measures].[Internet Sales Amount],
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember))
MEMBER [Measures].[YoY Growth %] AS IIF([Measures].[Prior Year Sales] = 0, NULL,
([Measures].[Internet Sales Amount] - [Measures].[Prior Year Sales])
/ [Measures].[Prior Year Sales])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Prior Year Sales], [Measures].[YoY Growth %]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].Members ON ROWS FROM [Adventure Works]Running Total
Business Use Case: Cumulative sales tracking — see how sales accumulate over months within a year.
WITH MEMBER [Measures].[Running Total] AS SUM(
PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Running Total]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Conditional Calculated Member
Business Use Case: Flag high-value product categories for executive reporting.
WITH MEMBER [Measures].[Sales Flag] AS IIF([Measures].[Internet Sales Amount] > 1000000,
"High Value", "Standard")
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sales Flag]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]
Calculated Member in a Non-Measures Dimension
Business Use Case: Create a virtual "Bikes and Accessories" combined category member for reporting.
WITH MEMBER [Product].[Product Categories].[Bikes and Accessories] AS [Product].[Product Categories].[Category].[Bikes]
+ [Product].[Product Categories].[Category].[Accessories] SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
{[Product].[Product Categories].[Category].Members, [Product].[Product Categories].[Bikes and Accessories]} ON ROWS
FROM [Adventure Works]Calculated Member Properties
Calculated members support optional properties that control formatting and solve order.
Syntax:
WITH MEMBER [Measures].[Name] AS Expression [, FORMAT_STRING = 'format']
[, SOLVE_ORDER = integer] [, BACK_COLOR = color]
[, FORE_COLOR = color]
[, FONT_FLAGS = integer]FORMAT_STRING Examples
WITH MEMBER [Measures].[Profit Margin %] AS
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount], FORMAT_STRING = "Percent"
MEMBER [Measures].[Avg Order Value] AS
[Measures].[Internet Sales Amount] / [Measures].[Order Count], FORMAT_STRING = "$#,##0.00"
SELECT
{[Measures].[Profit Margin %], [Measures].[Avg Order Value]} ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]SOLVE_ORDER
Controls evaluation priority when multiple calculated members intersect. Higher values are evaluated last (using values from lower solve-order calculations as inputs).
WITH MEMBER [Measures].[Gross Profit] AS
[Measures].[Internet Sales Amount] - [Measures].[Total Product Cost], SOLVE_ORDER = 1
MEMBER [Measures].[Gross Margin %] AS
[Measures].[Gross Profit] / [Measures].[Internet Sales Amount], SOLVE_ORDER = 2
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Total Product Cost], [Measures].[Gross Profit], [Measures].[Gross Margin %]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]