2 Replies Latest reply on Jan 2, 2014 7:30 AM by john.finn

# Creating a calculated field (% Difference) based on two other calculated fields

Hi,

I have created two calculated fields to show sales (and qty sold) based on a selected date range and selected date range minus 1 year.

I am now trying to create a % difference for both periods (current as a % of previous years sales). I am using a calculated field as follows:

Sales Diff=>   [Curr Sales] / [Sales -1 Year]

However this giving me NULL data. I have tried using SUM() but this causes the workbook to be unresponsive (presume it doesn't like this)...

Can anyone point me in the right direction please?

I have attached current test workbook.

• ###### 1. Re: Creating a calculated field (% Difference) based on two other calculated fields

John,

One thing that might make this a little easier is to calculate a new dimension instead of new measures.  When you have multiple measures, each calculated based on different values of a dimension, it does get very difficult to use them together and do further calculations on them.

I've attached a workbook that demonstrates a slightly different approach.  Please take a look and let me know if you have any questions.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 2. Re: Creating a calculated field (% Difference) based on two other calculated fields

Hi Joshua,

thanks for your reply. However this is not going to work for me in this case but it may under other circumstances in the future.

The workbook I had posted was a test workbook so it will not have outlined my end goal on this one.

On the LHS will be my ITEM Departments, Across the tab will be the values for each of my stores - these will be a combo or all of Sales TP (this period), Sales LP (last period), Sales Diff, % Sales Diff,  Qty TP, Qty LP, Qty Diff, % Diff.

I can't see how your solution would work for me in this case... (but thanks again)....