MDX Grand Total Issue

• Amit Chandra

  Old Hand

  Points: 372

  More actions

  October 4, 2016 at 2:36 am

  #147669

  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

• Martin Schoombee

  SSCoach

  Points: 19116

  More actions

  November 4, 2016 at 4:44 am

  #1910547

  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.