2 Replies Latest reply on Nov 30, 2012 12:15 AM by Grzegorz Mikulski

# Divide two dimensions - MDX calculated member

Hi,

I need some help with MDX calculated member. I connect to a cube using Tableu.

There are two dimensions that I want to use: [KPI].[Quantity] and [KPI].[Sales]. I want to divide sales by quantity to get the price (so in fact I divide two dimensions).

The only measure in my cube is [Amount].

 Entity Product Quantity Sales (EUR) Price Entity A Product A 100 100 ? Product B 50 150 ? Entity B Product A 30 36 ? Product B 40 160 ? Product C 80 640 ?

How can I calculate the Price? I am a real newbie with MDX formulas, so any help will be appreciated.

Thanks a lot!

Greg

• ###### 1. Re: Divide two dimensions - MDX calculated member

Hi Greg --

I don't think there are many people here expert enough with MDX to help you with the "how do I write an MDX statement to do this" part of your question. I'd suggest you try Microsoft's Analysis Services forums to address that part of your problem.

After you have an MDX statement that is working, you can fire it from Tableau using a Calculated Member. Here's how you open the dialog into which you'll paste your MDX:

Hope this helps, at least a little!

• ###### 2. Re: Divide two dimensions - MDX calculated member

Hello,

I was thinking where to put [Measures].[Amount] in the formula. But it turns out in Tableu I do not need the [Measures].[Amount] part. I only need to enter  [KPI].[Sales] / [KPI].[Quantity] - first time I tried this it did not work because I made a mistake in formula. In the example I gave a short version, but in fact full formula looks like this:

([Account].[Account BPM KPI].[Sales]) / ([Account].[Account BPM KPI].[Quantity])

But even better version is with IIF (that will handle div.by 0 error):

IIF([Account].[Account BPM KPI].[Sales]=0, NULL ,  ([Account].[Account BPM KPI].[Sales]) / ([Account].[Account BPM KPI].[Quantity]))

Works pretty well - so if anyone needs this for Tableu this is the way to go.

Thanks for the hint.

Regards,

Greg