IIf Grand total only totals FALSE values

Jul 26, 2013 at 8:09 PM
Hello all. I hope I'm not asking something that has been asked before. I have created a KPI that uses an IIf statement looking at a member value. For each leaf, the calc works fine, but for the Grand Total it does not. It seems that the IIf is being used against the Grand Total, instead of summing each leaf. See below.

098 92133.53 MDX calc result
099 97178.19 FALSE
732 40641.3 FALSE
734 112050.12 FALSE
739 57336.78 FALSE
747 72783.79 FALSE
796 720016.12 TRUE
799 0 FALSE
GT 7366463.25
Should be
8086479.37

I understand that IIf is immediate, and I've tried SUM(), but nothing seems to work. Ideas???

Thank you
Coordinator
Jul 26, 2013 at 8:35 PM
Can you post your current MDX query from the MDX tab?
Jul 26, 2013 at 8:51 PM
WITH
MEMBER [Measures].[Demand3] as IIf(
   [Loc #].currentmember.Membervalue = '796',
 
   ([Loc #].[All], [Sls E-Comm - Rtl]),
 
   ([Loc #], [Sls Tot - Rtl]) 
    - 
   ([Loc #], [Sls E-Comm - Rtl])
)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Location].[Loc #].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS  FROM [BDS Merchandising Week-Store-Sku] WHERE ([Time].[Fiscal Calendar].[FW Name].&[2013]&[2]&[4]&[12]&[51],[Measures].[Demand3]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Coordinator
Jul 26, 2013 at 9:28 PM
I think another IIf may help. Try this:

IIf(
[Location].[Loc #].CurrentMember is [Location].[Loc #].[All],
Sum([Location].[Loc #].CurrentMember.Children),

IIf(
[Loc #].currentmember.Membervalue = '796',

([Loc #].[All], [Sls E-Comm - Rtl]),

([Loc #], [Sls Tot - Rtl])
- 
([Loc #], [Sls E-Comm - Rtl])
)

)
Marked as answer by furmangg on 6/8/2014 at 4:31 PM
Jul 29, 2013 at 1:03 PM
Perfect! Thanks for the help!