MDX calculations using multiple time dimension elements

Question

We have an OLAP cube with the following dimensions: Ship_Date, Product, Customer. The measures are Units, Cost, List Price, Sell Price.

We want to set up calculations of Cost/Unit, List Price/Unit and Sell Price, like the below:

[Measure].[Cost]/[Measure].[Units]

This works with one time elements (All Ship Dates, 2016, Jan 2016, etc.) but when multiple elements are selected, like 2016 & 2015 or Jan 2016 & Feb 2016, the calculation does not resolve correctly which we think is because it is summing the ratios instead of calculating against the sums.

I tried the below calculations to similar results:

SUM([Ship_Date].CurrentMember.Level.Members,[Measure].[Cost]) / 
SUM([Ship_Date].CurrentMember.Level.Members,[Measure].[Units])

SUM(Descendants([Ship_Date].CurrentMember, [Month]), [Measure].[Cost]) / 
SUM(Descendants([Ship_Date].CurrentMember, [Month]), [Measure].[Units])

Aggregate(Descendants([Ship_Date].CurrentMember, [Date]), [Measure].[Cost]) / 
Aggregate(Descendants([Ship_Date].CurrentMember, [Date]), [Measure].[Units])

I'm very new to MDX - can someone please point me in the right direction?

Why aren't you setting up these measures in cube itself? Would be much less complicated abd definitely faster. — SouravA
Ideally our director would like to create these calculations outside of the cube so users without database experience would be able to edit them and create their own. We might need to go that route but I'd like to do due diligence first to see if this is an option. — Stevesaps

whytheq whytheq · Accepted Answer · 2016-12-24T07:26:07

Maybe try playing with the calculation's solve_order: https://msdn.microsoft.com/en-us/library/ms145539.aspx

DIVIDE(
   [Measure].[Cost]
  ,[Measure].[Units]
)
, SOLVE_ORDER = 1  //<<also maybe try -1 or -6500

MDX calculations using multiple time dimension elements

1 Answers