Aggregate function don't work with calculated measure.

Hi,

I can't get my AS2000 calculated measure to work with the Aggregate function.

We are using Excel as our frontend for our cubes and Excel is using the Aggregate function when the user selects multiple items for a filter dimension.

Here is the expression for my calculated measure:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])}, IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))

I have tried different solve orders for the measure: -7000, -1, 0, 1.

When I use solve orders < 0 I get following error: "The aggregate function cannot operate on measure ..."

When I use solve orders >=0 I get an empty result set.

Any idea how to get it to work?

Thanks, Christer

One idea is to move this calculation from calculated measure to calculated member in utility dimension, and then use SOLVE_ORDER=-1.|||

I'm already using a kinf of utility dimension (my account dimension is configured with formulas) and I'm pointing to the calculated measure from these formulas. But if I understand you correctly I should move the expression from the calculated measure into to my account formula and set the solve_order to -1, correct?

Thanks, Christer

|||Yes. In AS2000 this should be enough.|||

Ok - thanks I will try it! By the way how do I set the solve_order for my formulas? I have tried to put into the formula expression <expression>, solve_order=-1, but that gives me an formula error.

Thanks

|||If you are using CustomRollup formulas, you will need to create yet another column in the dimension table and point to it CustomMemberOptions. The syntax for it is something like FORMAT_STRING='Standard', SOLVE_ORDER=-1