4 Replies Latest reply on Jul 28, 2016 10:24 AM by Stoyko Kostov

# % Change between two columns

Hi,

I would like to show the percentage change between the FY16 and the FY15 MQL and SQL Columns on the "YoY" tab on the attached workbook. I would like it to be a separate column after FY16.

Is this possible?

• ###### 1. Re: % Change between two columns

Hi Lindsay,

The easiest thing to get the YOY percent change is the following:

1. Duplicate the MQL measure

2. Drag and drop the copy you just created to the Measure Values mark, just after the MQL

3. Right-click on the copy you just dropped and choose Quick Table Calculation - Percent Difference.

4. You get what you need - edit its alias to name it as you wish, e.g. MQL YOY %.

This has one small caveat: you get a column for the first year in your table - FY15 - as there is nothing to compare it against. If this doesn't bother you - just repeat the above steps for the SQL measure and you are done.

If it bothers you, I can only propose a hacky solution. You can't just add a filter on FY15 in MQL YOY %, as it will apply to the entire sheet and will hide other values that you want to see.

The hack that I propose is to create the MQL, MQL YOY%, SQL, and SQL YOY% in different worksheets, and then combine them in a dashboard in the order you need.

That way you can add filters separately on each worksheet, without affecting the other worksheets.

To create the MQL YOY% sheet, add a calculated field DiffNon0 with the following definition:

IF FIRST() <> 0 THEN

(ZN(SUM([MQL])) - LOOKUP(ZN(SUM([MQL])), -1)) / ABS(LOOKUP(ZN(SUM([MQL])), -1))

ELSE 0

END

Make sure it is computed along Table(Across).

Then drag DiffNon0 to Text and to Filter, and exclude the 0 values. That will remove the first column.

I'm attaching the workbook I prepared: from your workbook I only kept the YoY sheet, and then created YoY Table Calc Easy with the first solution I propose above, and sheets MQL and MQL YOY% and a dashboard with the second solution I proposed.

Hope this helps - don't hesitate to ask if you have more questions!

2 of 2 people found this helpful