Post reply

Percentage of Parent - Grand Total showing #NUM!

  • May 29, 2014 at 5:37 am

    #151354

    Hi guys,

    I have a Percentage of Parent calculated member, and in the Grand Total it is showing #NUM!

    Anyone can shed some light on how to get around this please?

    Calculation:

    CREATE MEMBER CURRENTCUBE.[% Turnover of Parent]

    AS

    (Axis(0).Item(1).Item(0).Dimension.CurrentMember, [Measures].[Sales Amount])

    /

    ( Axis(0).Item(1).Item(0).Dimension.CurrentMember.Parent, [Measures].[Sales Amount])

    ,

    FORMAT_STRING = "Percent"

    Thanks in advance!

  • May 29, 2014 at 8:42 am

    #1717372

    I'm thinking "Grand Total" isn't part of the dimension you are using on Axis 0 but rather something that's generated by the VS browser. So if "Grand Total" isn't a dimension member then it won't have a parent and the calculation will fail.

    I'm on LinkedIn

  • May 29, 2014 at 8:44 am

    #1717374

    Exactly. The Grand Total is shown in the pivot table within Excel. So is there a way to properly calculate the Grand Total? Ideally the Grand Total should show 100%..

  • May 29, 2014 at 9:00 am

    #1717382

    You can just use something like IIF((your calc) = 0, NULL, (your calc)) / IIF((your calc2) = 0, NULL, (your calc2)) to stop the error.

    Change the NULLs to 1 if you want it to show 100%

    I'm on LinkedIn

  • May 29, 2014 at 9:03 am

    #1717384

    Good insight thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply