none
MeasureExpression Property - How to use

    Question

  • I have a cube with several MeasureGroups.

    I want to provide a calculated measure "Average Sale Price" which is calculated as "Gross Revenue" / "Product Count".

    Both the "Gross Revenue" and "Product Count" measures belonging to the same measure group ... "Royalty Statement".  Ideally I would like the "Average Sale Price" measure also to belong to the "Royalty Statement" Measure Group also.

    I am therefore trying to use a MeasureExpression calculation on a regular measure, as opposed to a Calculated Member to achieve the calculation. (or can someone advise how to make a Calculated Member appear within a specific Measure Group ?)


    On the measure properties
     - "AggregateFunction" is set to "Sum"
     - "MeasureExpression" is set to "[Measures].[Gross Revenue] / [Measures].[Product Count]"

    On deploying the cube I get ... "Error 1 Errors in the metadata manager. The 'Product Count' right operand of the measure expression of the 'Average Sale Price' measure cannot belong to the same measure group. "

    Can I really not have a MeasureExpression with the numerator and denominator in the same Measure Group ?

    I have futzed with things a bit including getting the cube to process by using a denominator from a different MeasureGroup, and playing with the Source object binding.

    What am I missing ?

    Thanks in Advance

    Marcus

    Wednesday, September 27, 2006 7:17 PM

Answers

  •  

    Unfortunately the answer is no. This is a common request from customers and is planned to be implemented in our next release. For Yukon, you could use the following work around: if you want to associate the new calculated member with specific dimensions, you could set the non-empty behavior for the calculated member to a measure from a measure group that intersects with those dimensions.

    Hope this helps,

    Artur

    Monday, October 02, 2006 8:04 PM

All replies

  • No. I do not think that you can use the same measure group in measure expressions. This is the new version of Lookup cube(MDX) in AS2005 with limited support for calculations except (*, /)

    You can achive the same with a calculated member.

    Regards

    Thomas Ivarsson

    Wednesday, September 27, 2006 7:33 PM
    Moderator
  •  

    Marcus,

    You can easily associate your calculated measure with any existing measure group. To do this, please follow these steps:

    - Open your project in SQL Server Business Intelligence Development Studio & double click your cube

    - In the Cube editor, go to the Calculations tab and double click your calculated member "Average Sale Price"

    - In the toolbar, just right from the "Form View" and "Script View" toolbar buttons, click on the "Calculation Properties" toolbar button

    - In the Calculation Properties dialog that comes up, select your calculated member "Average Sale Price" from the dropdown and set the associated measure group for it to be "Royalty Statement"

    - Deploy the project and you are ready to go

    Hope this helps,

    Artur

    Wednesday, September 27, 2006 9:23 PM
  • Thanks Artur, that helps.

    If I do this, can I also assume that the Dimension Usage characteristics for that Measure Group now apply to the Calcuated Member ?

    Marcus

    Thursday, September 28, 2006 6:43 PM
  •  

    Unfortunately the answer is no. This is a common request from customers and is planned to be implemented in our next release. For Yukon, you could use the following work around: if you want to associate the new calculated member with specific dimensions, you could set the non-empty behavior for the calculated member to a measure from a measure group that intersects with those dimensions.

    Hope this helps,

    Artur

    Monday, October 02, 2006 8:04 PM