# Creating a Calculated Member using another Calculated Member

• ### Question

• Is it possible to create a Calculated Member using an already existing Calculated Member? I tried and there is no Syntax error but the cell shows no real value in the data cell under the browser tab, it shows "#VALUE!" Any advice?

Thanks

SV

Wednesday, May 1, 2013 1:38 PM

• Hi ,

I think the link below will surely help you for creating the calculated member in cube and solve the issue you are having.

http://msdn.microsoft.com/en-us/library/ms166568.aspx

The issue seems your pre calculated member occurring with multiplying by 0 or divide by 0 issue if you simply doing SUM, well you can handle by using IIF or case statement.

http://www.bidn.com/blogs/Anil/ssas/2662/showing-measure-values-as-either-%E2%80%980%E2%80%99-or-%E2%80%98null%E2%80%99-in-cube

If A and B are calculated measures and you are creating new calculated member as Measure C, then you need to check for this.

```IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e

IIF(

ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,

IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]*[MEASURES].[B])

)```

Thanks,

Anil Maharjan

• Proposed as answer by Friday, May 3, 2013 8:16 AM
• Marked as answer by Thursday, May 9, 2013 9:29 AM
Thursday, May 2, 2013 5:29 AM

### All replies

• Yes. It is perfectly possible. Maybe you have a logic error. Try to do a dummy test, like just showing the same value from the first Calculated Member in the second Calculated Member.

Let me know how it goes.

HTH.

PS. Mark this post as an Answer if it helps you to solve your problem or question. Thanks!

Alan Koo Labrin | "Microsoft Business Intelligence y más..." http://www.alankoo.com

Wednesday, May 1, 2013 3:58 PM
• Thanks Alan Koo its working. The question i'm having is , lets say i created a calculated member based on product of two different measures ( the sum is done automatically), my questionis when i use SUM function on this already created calculated member it gives me the "#VALUE!".

SV

Wednesday, May 1, 2013 4:17 PM
• Hi ,

I think the link below will surely help you for creating the calculated member in cube and solve the issue you are having.

http://msdn.microsoft.com/en-us/library/ms166568.aspx

The issue seems your pre calculated member occurring with multiplying by 0 or divide by 0 issue if you simply doing SUM, well you can handle by using IIF or case statement.

http://www.bidn.com/blogs/Anil/ssas/2662/showing-measure-values-as-either-%E2%80%980%E2%80%99-or-%E2%80%98null%E2%80%99-in-cube

If A and B are calculated measures and you are creating new calculated member as Measure C, then you need to check for this.

```IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e

IIF(

ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,

IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]*[MEASURES].[B])

)```

Thanks,

Anil Maharjan

• Proposed as answer by Friday, May 3, 2013 8:16 AM
• Marked as answer by Thursday, May 9, 2013 9:29 AM
Thursday, May 2, 2013 5:29 AM
• Hi saivenkat77 ,

It seems that the Sum function is not set correctly . Are you browsing your data from the browser (SSMS) ? if so, please locate the mouse on the #VALUE! cell and share the specific problem .

Regards, David .

Sunday, May 5, 2013 3:46 PM