6 Replies Latest reply on Oct 31, 2018 6:55 AM by Tony Nguyen

Variance of 2 columns of the same Measure

Hello All,

I'm new to Tableau and have been self teaching for about a week now. I have a good background from Excel. Anyhow I have a set of data that I can't seem to get a variance between the years. I've done some research online and on forums to no luck.

I tried do some calculated fields to no avail. I was hoping someone can help me out here. I have added a snippet of the data. Thank you for taking the time to read this, any help would be much appreciated!

Note - the data set is for 2017-2018, I know I can adjust that, but for whatever reason if I have that year range the "Make" column overlaps it, and covers a good portion of it.

Is what I requested possible with the current setup? Once again thank you for any type of input!

Cheers,

Tony

EDIT - I have converted the "Report Year" to be recognized as a Date instead of numbers

• 1. Re: Variance of 2 columns of the same Measure

You need to Format the Dimension Make and Left Justify it. Is that you need?

• 2. Re: Variance of 2 columns of the same Measure

Hi Deepak,

Essentially all I want to do is subtract 2017 and 2018 to see any growth. For example in 2018 there were 2,863 units sold and 2017 there was 2,321 sold. I want to create a column that shows this difference.

So the column (For Acura) would have 542. Does that clarify my question?

Thank you for responding so quickly,

Tony

• 3. Re: Variance of 2 columns of the same Measure

Yes use this calc

SUM({FIXED Make:SUM(If Year(Report Year)=2018 then Number of Records END)})-SUM({FIXED Make:SUM(If Year(Report Year)=2017 then Number of Records END)})

1 of 1 people found this helpful
• 4. Re: Variance of 2 columns of the same Measure

Wow what a formula!

I will go through on my personal time to dissect that, but it seems that the calculation is summing the 2 columns. Please snippet below -

Unless I didn't format it correctly? I would like to say thank you Deepak for taking the time to help me through this!

Tony

• 5. Re: Variance of 2 columns of the same Measure

Looks like you added them. When you get Correct results, you please close thread by marking my Reply as Helpful and CORRECT

• 6. Re: Variance of 2 columns of the same Measure

Hey Deepak,

Please see below for the snippet of my workbook. The header with Forum Help is your formula. I tinkered with it to see what is happening and no matter what I did the numbers never quite lined up to what I wanted.

The Unit Change header is what I saw from another forum which is - SUM([Total])-Lookup(ZN(SUM([Total])),1)

Which solved my problem, sort of, now I can't hide the 2017 column for JUST 2017 Unit Change, it hides both the Total and Unit change column 2017's. Regardless, that's minor.

Thank you again Deepak for all the help!

Tony