Adding complex measures in MDX

Question

I have successfully added basic sum/count/etc.. simple measures, but have no idea how to add more complex one.

Let's assume that data structure represent tracks with goods and have next structure:

• date
• id_track
• id_articte
• amount
• cost

And want to calculate next measures:

1. average item price, calculated as sum(cost) / sum(amount)
2. average track cost. It's average between sum(cost) for whole tracks. Sql expression looks like sum(cost) / count(distinct id_track)

What is the right way to add this measures to my cube, in case i will need them in Excel over XMLA?

Answer 1

Complex calculation are solved using what we call in MDX 'calculated measures'.

Calculated Measures support the whole power of MDX language. You can see more information about Calculated Measures here. They are very powerful and over 100 functions are supported (function list).

If you want to define a calculated measure once per schema I'd advise defining them in Advanced/Scripts in the Builder UI tab. You can first check validity in the MDX IDE and once validated move them to the Script.

Answer 2

average item price, calculated as sum(cost) / sum(amount)

This would look something like the following:

WITH MEMBER [Measures].[AvgPrice] AS
  AVG(
    EXISTING([Item].[Item].MEMBERS)
   ,[Measures].[COST]
  )
...

Or

WITH MEMBER [Measures].[AvgPrice] AS
  [Measures].[COST] 
  / 
  [Measures].[AMOUNT] 
...

average track cost. It's average between sum(cost) for whole tracks. Sql expression looks like sum(cost) / count(distinct id_track)

WITH MEMBER [Measures].[AvgTrackCost] AS
  AVG(
    EXISTING([TrackItem].[TrackItem].MEMBERS)
   ,[Measures].[COST]
  )
...

I've had to guess that the following exist within your cube:

[Measures].[COST]
[Measures].[AMOUNT] 
[Item].[Item].MEMBERS
[TrackItem].[TrackItem].MEMBERS