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

# Combine Measures

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

Kelly,

[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

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

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