trying to understand mdx calculations

Question

I'm trying to understand the math behind this MDX query.

TL;DR: how does this query run? (how will "div" be calculated?)

WITH 
member [Customer].[Country].[x] as (([Customer].[Country].[Australia]) - ([Customer].[Country].[France], [measures].[sum sales]))
member [measures].[div] as ([Measures].[Sum Sales] / [Measures].[Sum Cost])

SELECT  
{ [measures].[div] } 
ON COLUMNS, 
{ [Customer].[Country].[x] } 
ON ROWS

Long story:

I have a hierarchy of [Customer].[Country] and measures sales and cost.

For this query:

SELECT  
{ [Measures].[Sum Sales], [Measures].[Sum Cost] } 
ON COLUMNS, 
{ [Customer].[Country].[Australia] , [Customer].[Country].[France] } 
ON ROWS

I get the result of:

Australia: cost = 8266005, sales = 18005422
France:    cost = 4478131, sales = 9610041

I create this member:

// substract france from australia
member [Customer].[Country].[x] as (([Customer].[Country].[Australia]) - ([Customer].[Country].[France]))

and ran the query with it instead of the 2 members:

WITH 
member [Customer].[Country].[x] as (([Customer].[Country].[Australia]) - ([Customer].[Country].[France]))

SELECT  
{ [Measures].[Sum Sales], [Measures].[Sum Cost] } 
ON COLUMNS, 
{ [Customer].[Country].[x] } 
ON ROWS

and got the result of:

x: cost = 3787874, sales = 8395381

This makes sense. Then I change france to use sales in the member, like this:

WITH 
member [Customer].[Country].[x] as (([Customer].[Country].[Australia]) - ([Customer].[Country].[France], [measures].[sum sales]))

SELECT  
{ [Measures].[Sum Sales], [Measures].[Sum Cost] } 
ON COLUMNS, 
{ [Customer].[Country].[x] } 
ON ROWS

That got this result:

x: cost = -1344036, sales = 8395381

and i can see that for australia, the measure from the column is being used and for france the sales in the tuple is the "strong" one and is keeped over the cost. meaning that the "cost" substraction is actually (AU,cost) - (FR,sales).

So I try this (HERE IS THE PROBLEM!!!)

I set a custom measure:

member [measures].[div] as ([Measures].[Sum Sales] / [Measures].[Sum Cost])

And put it on the columns with the other measures:

WITH 
member [Customer].[Country].[x] as (([Customer].[Country].[Australia]) - ([Customer].[Country].[France], [measures].[sum sales]))
member [measures].[div] as ([Measures].[Sum Sales] / [Measures].[Sum Cost])

SELECT  
{ [Measures].[Sum Sales], [Measures].[Sum Cost], [measures].[div] } 
ON COLUMNS, 
{ [Customer].[Country].[x] } 
ON ROWS

The result for x sales and cost is the same. "div" = -6.2463950147143. How is it calculated? I'd guess that it should be:

(AU,sales) / (AU,cost) - (FR,sales) = 18005422 / 8266005 - 9610041 = -9610038.82175

But that's wrong...

I took out sales from france

 member [Customer].[Country].[x] as (([Customer].[Country].[Australia]) - ([Customer].[Country].[France]]))

and got the expected results:

 div = ((AU,sales) - (FR,sales)) / ((AU,cost) - (FR,cost)) = (18005422 - 9610041) / (8266005 - 4478131) = 2.216383

Thanks for any and all help

Imbar M. Imbar M. · Accepted Answer · 2016-09-24T16:14:25

Finally it came to me!

The number I got is:

div = ((AU,sales) - (FR,sales)) / ((AU,cost) - (FR,sales)) = (18005422 - 9610041) / (8266005 - 9610041) = -6.2463959

When ever france needed a measure to use in the calculation, it got sales. That is the rule.

Hope this will help someone in the future, although no one seemed to be intrested in this... :(

trying to understand mdx calculations

1 Answers