SSAS Grand Total for calculated measure in excel not working

0
votes

I have an ssas cube with a calculated member called cumulative ptp's. Everything seems to work in excel except for the Grand Total which is displaying #Value!. I'm not sure where the problem is. I've added a few pics of what I have done. Any help will be most appreciated. Thanks in advance. Calculated Member Excel Results

IIF
(
[Day-Month Analysis].[Day].FIRSTCHILD = [Day-Month Analysis]. [Day].CURRENTMEMBER,

 Sum(
{[Day-Month Analysis].[Day].FIRSTCHILD:[Day-Month Analysis].  [Day].CURRENTMEMBER},
[Measures].[Sum of IsPTP]
)
,  SUM(Measures.[Sum of IsPTP])
)

So I downloaded that extension and the MDX error is: "Range operator (:) operands' have different levels; they must be the same"

1
sqlexcelssasmdx
Can you install OLAP PivotTable Extensions and run this feature to get an error message for the #VALUE! cell? olappivottableextend.codeplex.com/… – GregGalloway
Pasting your MDX into this thread will save someone time retyping it from the image. Please do this. – GregGalloway
Hi Greg I have added the code. I am also looking into that link that you shared. Thanks for your response. – Liam Domingo

1 Answers

1
votes

Try this:

Sum(
{[Null:[Day-Month Analysis].[Day].CURRENTMEMBER},
[Measures].[Sum of IsPTP]
)

I don't think you need the IIf. If that doesn't work right describe what's in that [Day] hierarchy further.