2 Replies Latest reply: Oct 16, 2015 11:09 AM by Andy Weir RSS

    Calculating members of a dimension

    Simon Hogg

      It's Friday afternoon, I'm tired, and I can't think of the correct answer so I'm hoping someone here can put me straight;

       

      How do I perform calculations within a dimension?  For example, let's suppose I have a country dimension in my database.  How do I compare one country to another?

       

      Eg:

       

      France               <--- from database

      Germany            <--- from database

      Spain                 <--- from database

       

      Spain as % of (France+Germany+Spain).    <--- this is calculated.

       

      (I'm actually trying to re-create a P&L with margin calculations;

           Sales,

           Commission,

           Commission as % of Sales

      the dimension is the lines of the P&L, the measures are "Actuals", "Forecast", etc.)

        • Re: Calculating members of a dimension
          Henric Cronström

          If France, Germany and Spain are the three selected (or possible) countries, you can create a pivot table that shows Sum(Sales) / Sum(total Sales). This will be the percentages.

           

          HIC

          • Re: Calculating members of a dimension
            Andy Weir

            If you actually wanted to find the differences between one country and another you could use an extension (like qsVariable but I belive there are others) and create variables vCountryA and vCountryB to setup a sheet that would allow to compare like with like.

             

            sum({$ <Country={$(vCountryA)}>} Yourmeasure) Vs sum({$ <Country={$(vCountryB)}>} Yourmeasure)

             

            Hope this helps although on a Friday I may have just complicated this

             

            Regards

             

             

            Andy