October 4, 2016 at 2:36 am
Folks, I am back with the classic grand total issue that we face in MDX. I thought I clearly had my head around the fix but this thing keeps stumping me every now and then. So here is the problem -
I want to calculate a calculated measure at a specific granularity of the 2 dimensions - 'Fiscal year' & 'Currency conversion'. I scope my calculated measure - [Amount] along with the 2 dimensions and do the necessary calculation. Here is the snippet -
SCOPE
([Measures].[Amount],
[Currency Conversion].[Currency Conversion].[Currency Conversion].MEMBERS,
[Date].[Fiscal Year].[Fiscal Year].MEMBERS
);
THIS =
IIF(([Date].[Fiscal Year].Currentmember, [Measures].[Budget Rates]) = NULL,[Measures].[Amount FX]
,
([Date].[Fiscal Year].Currentmember, [Measures].[Budget Rates]) * SUM([Measures].[Amount FX])
);
END SCOPE;
I get the granular level data correct with this but grand totals are off. Can anyone help?
Thanks
November 4, 2016 at 4:44 am
Disclaimer: I'm answering this purely from "muscle memory", and haven't tested or verified it...but I think it's worth a try.
I think the problem here is your dimensional reference, which steps down to the lowest level. I'd replace
Currency Conversion].[Currency Conversion].[Currency Conversion].MEMBERS
with
Currency Conversion].[Currency Conversion].MEMBERS
to include the "ALL" member of the dimension as well. You may need to add an aggregate here to make that work.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply