13 Replies Latest reply on Feb 14, 2020 3:51 PM by Robert Breen

# Trouble creating simple calculation of % Chg vs YA

Hi,

I have a workbook that someone on the forums has been helping me get converted to my needs and the progress has been tremendous and I am down to my final step.

Because of how we needed to convert measures to time frames I am struggling with creating a calculated field that takes (Sales for CUR time frame - Sales for YAG time frame)/Sales for YAG time frame.

I would appreciate any help on this. Once I see how you are able to solve this I think I can figure out how to create any other calculated fields based on this.

Thanks

Greg

• ###### 1. Re: Trouble creating simple calculation of % Chg vs YA

Hello Greg,

You can add % difference with a quick table calculation. Like the image below. I attached the updated workbook with two new columns.

Hope this helps! See Attached.

Robert Breen

1 of 1 people found this helpful
• ###### 2. Re: Trouble creating simple calculation of % Chg vs YA

Hey Grey

Would the attached workbook work? Or do you want it more formatted?

//Rohit

1 of 1 people found this helpful
• ###### 3. Re: Trouble creating simple calculation of % Chg vs YA

I think this:

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

2 of 2 people found this helpful
• ###### 4. Re: Trouble creating simple calculation of % Chg vs YA

Hi Robert,

For some reason the values from your suggestion are not correct as rows that have sales greater in the current time frame than the YAG time frame yet the % change number is negative.

I also have some other calculations that would be something other than one of the quick calculations.

• ###### 6. Re: Trouble creating simple calculation of % Chg vs YA

Hello Greg,

Make sure you are looking at the units percentage separately from the \$ percentage. If sales are going up vs the previous value, then the value will always be positive. If you have more complex calculations, you can also write them out manually. Here's the sales example as a calculation.

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

Hope this helps! See Attached.

Robert Breen

• ###### 7. Re: Trouble creating simple calculation of % Chg vs YA

Deepak,

I need any total rows to populate a number too.

• ###### 9. Re: Trouble creating simple calculation of % Chg vs YA

Hi Robert,

Yes I am, if you look at \$ % change for the Grand total line it shows -4.81% when the answer is actually +5.0%

• ###### 11. Re: Trouble creating simple calculation of % Chg vs YA

Hello.

Try this file. It was calculating backwards.

1 of 1 people found this helpful
• ###### 12. Re: Trouble creating simple calculation of % Chg vs YA

Hi Robert,

This works perfect. I ultimately want to either remove or hide the YAG columns but not necessarily for both sales and units. Right now when I hide it hides YAG for both. When I try to filter out YAG it makes your calculation disappear?

• ###### 13. Re: Trouble creating simple calculation of % Chg vs YA

Hello Greg,

If you want to hide that column, you'll have to restructure all of the calculations. I set it up for you. See attached.