Search code examples
mdxcalculated-field

MDX - Applying a month summary value to a dimension

I am trying to apply a measure value calculated at the Month level to a dimension contained within that month i.e.

Before

Should look like this:

After

I've attempted to use a scoping statement so far but with no luck.

SCOPE (
   {[Sale].[Sale Year].&[2]:[Sale].[Sale Year].&[7]}
            ,[Date].[Calendar Month].&[201603]
            ,[Measures].[Costs Per Sale] );

THIS = ([Date].[Calendar Month].&[201603],[Measures].[Costs Per Sale]);
END SCOPE;

The Aggregated Sales measure is calculated using the Sale Year which unfortunately has not and cannot be linked to the Cost dimension.

Does anyone know how I can apply the Cost Per Sale monthly value to the [Sale].[Sale Year] dimension?

Thanks

Solution

  • Try this - 

    SCOPE ({[Sale].[Sale Year].&[2]:[Sale].[Sale Year].&[7]} ,[Measures].[Costs Per Sale]);    
THIS = ([Sale].[Sale Year].[All],[Measures].[Costs Per Sale]);
END SCOPE;