locked
Creating a Calculated Member using another Calculated Member RRS feed

  • 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

Answers

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]) 
     
       )

    Hope this might help you .

    Thanks,

    Anil Maharjan

    • Proposed as answer by Bharath_RAM Friday, May 3, 2013 8:16 AM
    • Marked as answer by Elvis Long 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