Controlling Aggregations on Calculated Members

[disgustipated]

Hi,

I've got an issue with my cube. I'm trying to perform a calculation using a calculated member, but when the totals are calculated, the value isn't the sum of the above.

eg, In Column E in the below table, I'd like to see 24 rather than 22.75. Analysis server seems to do the same calculation as defined in the calculated member (Column C) rather than just totalling the above.

Is there a way around this?

		<TABLE id="Table1" cellSpacing="1" cellPadding="1" width="300" border="1">
			<TR>
				<TD>Item</TD>
				<TD>Col A</TD>
				<TD>Col B</TD>
				<TD>Col C</TD>
				<TD>Col D</TD>
				<TD>Col E</TD>
			</TR>
			<TR>
				<TD></TD>
				<TD>Sales</TD>
				<TD>Returns</TD>
				<TD>% (B/A)</TD>
				<TD>Likely Sales</TD>
				<TD>Likely Returns (D*C)</TD>
			</TR>
			<TR>
				<TD>Apples</TD>
				<TD>10</TD>
				<TD>2</TD>
				<TD>20%</TD>
				<TD>80</TD>
				<TD>16</TD>
			</TR>
			<TR>
				<TD>Bananas</TD>
				<TD>30</TD>
				<TD>5</TD>
				<TD>16.6%</TD>
				<TD>50</TD>
				<TD>8</TD>
			</TR>
			<TR>
				<TD>Total</TD>
				<TD>40</TD>
				<TD>7</TD>
				<TD>17.5%</TD>
				<TD>130</TD>
				<TD>22.75</TD>
			</TR>
			<TR>
				<TD></TD>
				<TD></TD>
				<TD></TD>
				<TD></TD>
				<TD></TD>
				<TD></TD>
			</TR>
		</TABLE>

Thanks for your help,

Scott

 

[justins57]

It easier to solve using your MDX. Can you provide the MDX?

Try this:

MEMBER [Dimension].[Total] as 'IIF(Measures.CurrentMember IS Measures.[% (B/A)],Avg({Apples + Bananas{),Sum(Apples,Bananas))'