Understanding Calculated Members

Calculated members are calculations in a cube that are evaluated when a query is performed. They can be configured on a dimension in a Main or Alternate hierarchy. Calculated Members return a single member from a dimension. However, it is generally a combination of multiple members that results in the single member returned. For example, if you want to extrapolate the average unit price for all units in all locations within North America, you would have to include many account and product dimension members to return the one member, which is average unit price.

Calculated members contain a formula that allows further financial analysis. The formulas for creating calculated members are created using universally accepted formula syntax for accessing multi-dimensional databases known as Rules or Multi-dimensional Expressions (MDX).

Some examples of calculated members are:

[Gross Sales] = [Sales]+[Freight Revenue]-[Sales Returns]

[Net Sales] = [Gross Sales]-[Sales Discounts]-[Sales Deductions]+[IC Sales]

[Operating Profit Margin] = ([Gross Profit]-[Operating expenses])/[Net Sales]

[Shipping Expenses] = {[Total Departmental Expenses],[Shipping Department]}

[Previous Month] = [Current Month].lag (1)

[Previous Quarter] = [Current Quarter].lag (1)

[Previous Year] = [Current Year].lag(1)