3 Replies Latest reply on Feb 22, 2016 5:18 PM by kelly.schmittel

    Combine Measures

    kelly.schmittel

      I would like to combine measures so that they will display on a single line and then I would like to do YoY comparisons. Please see below. I have PO 2014 that displays Jan 2014-December 2014 on row 1 and PO 2015 that displays Jan 2015-Dec 2015 on row 2. How do I get them on the same row so it shows Jan 2014 - December 2015? Can I do a calculated field to do this? I would also like to do YoY growth by month. Thanks.

       

        • 1. Re: Combine Measures
          Shinichiro Murakami

          Kelly,

           

          Add calculated field.

           

          [PO consolidated]

          ifnull(sum([Po 2014]),0)+ifnull(sum([Po 2015]),0)

           

          Then create "month" and "Year" field

           

          [Year]

          year([Date])

           

          [Month]  //  01~12 are used for correct sorting order.

          right(str(100+month([Date])),2)+datename('month',[Date])

           

          Then put table calc down the table. for [PO consolidated] with percent diff from previous

           

          Thanks,

          Shin

           

          9.0 attached.

          • 2. Re: Combine Measures
            Steve Mayer

            Kelly -

             

            In the attached workbook, I mocked up your data to include measures for PO2014 and PO2015, and then used a calculated field to be able to display them on one line. I'm not sure how you want to handle the PO LE1 line item, so I have left it out for now. It would be easy to include in the calculation if you need it. In a perfect world, your data would have not split the PO measure into two measures - one for 2014 and one for 2015, but we can easily combine them if this is the way you are getting the data.

             

            Here are the steps:

             

            Step 1: Create a calculated field which calculates the total based on the Year. This assumes no date overlap between the PO2014 and PO2015 measures:

             

            [Po]

             

            IF YEAR([Month End]) = 2014 THEN

                [Po2014]

            ELSE

                IF YEAR([Month End]) = 2015 THEN

                    [Po2015]

                END

            END

             

            Step 2: Drag [Month End] to Columns and set up Measure Values to use the new calculated field [Po]. Expand [Month End] to show Months.

             

            You should now see the values on one line.

             

            To show YOY%, you need to set up a Table Calculation on the [Po] calculated field with the following settings. I included a YOY% in the attached workbook.

             

            Hope this helps,

             

            • 3. Re: Combine Measures
              kelly.schmittel

              Steve - your solution worked, but why won't it allow me to create a line chart with the percent difference?