1 2 Previous Next 18 Replies Latest reply on Nov 15, 2018 1:23 PM by Adam Elbayar

# Difference between two columns

Hi,

I have two columns (2017 and 2018 see below) that I am trying to find the difference from rather than the total. Can someone help me set up the formula? I have been trying to follow other examples here but have been unsuccessful.

• ###### 1. Re: Difference between two columns

Hi Adam,

You can use a table calc to accomplish this.  I don't know the name of the measure you're using, so for this example I'll call it sales.

lookup(sum([sales]),last()) - lookup(sum([sales]),first())

Make sure it's set to compute using Table across.  Let me know if you have any questions.

Best,

Paul

1 of 1 people found this helpful
• ###### 2. Re: Difference between two columns

Hi Adam,

Find my approach as reference below and stored in attached workbook version 2018.2 located in the original thread

1. Right click Sales Copy > Quick Table Calculation > Difference

At the same time find attached thread on topic "Quick Table Calculations"

Hope it helps,

Regards

Norbert

1 of 1 people found this helpful
• ###### 3. Re: Difference between two columns

Hi Adam,

There are a few ways you can accomplish this, one is what Paul mentioned. Another one is to grab the values for each year and then subtract.

1. create 2 calculated fields 2018 and 2017 sales->

2018

if DATEPART('year', [Order Date]) = 2018

THEN [Sales]

END

and  2017

if DATEPART('year', [Order Date]) = 2017

THEN [Sales]

END

2. then calculate difference 2018-2017

SUM([2018])-SUM([2017])

Hope this helps.  Let us know for questions.

Thanks,

Soham

1 of 1 people found this helpful
• ###### 4. Re: Difference between two columns

Hi Adam,

Here is the work around for your request.

Created a calculated filed for each year sales and another calculated field used to take difference like below.

2014 Sales:

2015 Sales

Total Difference:

Attached workbook for your reference.

1 of 1 people found this helpful
• ###### 5. Re: Difference between two columns

It worked. Thank you so much.

• ###### 6. Re: Difference between two columns

All,

The formula worked but now I am trying to hide the difference from showing under the 2017 column. Any ideas?
Thank you again for the help!

• ###### 7. Re: Difference between two columns

Sure you just need to add an if statement in front like this:

If [year] = 2018 then lookup(sum([sales]),last()) - lookup(sum([sales]),first()) end

That way if it's 2017 it won't show anything

Best,

Paul

1 of 1 people found this helpful
• ###### 8. Re: Difference between two columns

hm, when I input that it says that im missing parenthesis.

• ###### 9. Re: Difference between two columns

Can you paste a screenshot so I can see where it's underlining in red?  I don't see any parenthesis missing.

• ###### 10. Re: Difference between two columns

Sure!
IF [Year]=2018 THEN(SUM([Attended]),LAST()) - LOOKUP(SUM([Attended]),FIRST()) END

• ###### 12. Re: Difference between two columns

sorry here is the clear picture

• ###### 13. Re: Difference between two columns

Oh, it looks like you're missing the word "LOOKUP" in front of the first (Sum(Attended)

• ###### 14. Re: Difference between two columns

looks like now there is an even bigger problem. I am so confused. I appreciate your help though

1 2 Previous Next