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

    Divide two dimensions - MDX calculated member

    Grzegorz Mikulski

      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].

       

      EntityProductQuantitySales (EUR)Price
      Entity AProduct A100100?
      Product B50150?
      Entity BProduct A3036?
      Product B40160?
      Product C80640?

       

      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
          Russell Christopher

          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:

           

          ScreenHunter_03 Nov. 29 16.09.gif

           

          Hope this helps, at least a little!

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

            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