3 Replies Latest reply on Dec 14, 2016 6:00 AM by Simon Runc

    How to create measure based on 1 value in dimension

    Paul Damen

      Hi,

       

      I have dimension with the following fields:

      - Revenue

      - Available rooms

      - Occupied rooms

       

      And 1 measure which is called "value" and contains all the values.

       

      What I want to do is created individual measures for the 3 dimensions. So be able to drag the measure Room Revenue on the canvas. Or create a ration where I divide the value for occupied rooms by the value of available rooms

       

      How can I create these formulas easily?

       

      Regards, Paul

        • 1. Re: How to create measure based on 1 value in dimension
          Simon Runc

          hi Paul,

           

          So yes you can...

           

          If you create a formula like this

          [Revenue Value]

          IIF([Dimension] = 'Revenue', [Value], NULL)

          That will give you a separate field which only contains the values for 'Revenue'...and you can do a similar thing for the other 2.

           

          Once you have these you can create ratios by aggregating the results from these new fields. eg. SUM([Revenue Value])/SUM([Available Rooms Value])

           

          Hope that helps.

          • 2. Re: How to create measure based on 1 value in dimension
            Paul Damen

            Hi Simon,

             

            Thanks for the quick reply.

             

            So this always have to be done through If statements? There is no way to write 1 formula to create this?

             

            Regards, Paul

            • 3. Re: How to create measure based on 1 value in dimension
              Simon Runc

              It depends on what you want to do with the data and how you want to display it....

               

              So if you just wanted to show the SUM (say) of the 3 values, you can just drag values into your Viz and then the Dimension on columns (say0...so this would then give a SUM per dimension.

               

              Alternatively (and is generally more typical) your data would come in with separate columns for each measure (so you would no longer have this measure dimension, but would have 3 measures called 'Revenue', 'Available Rooms' and 'Occupied Room'). This might also be better as you probably want different formats for each measure (Revenue in £, and the other 2 as integers), which you'd need to do in the data source (below is a quick example using a pivot table in Excel)

               

               

              There are times when having a dimension for a measure is useful, but generally, measures would be in separate columns, and (as my example here) Hotel, as a real dimension (something you want to slice the data by) would go down the page.

               

              but if you want to have separate fields per measure and you are restricted to having the data as is, then yes you'll need to create a calculated field per measure.