8 Replies Latest reply on Sep 16, 2016 1:05 PM by isa.serah

# How to calculate difference or variance in average sales of current year and previous year in tableau?

I want to get the average sales of current year and average sales of previous year (by creating 2 calculated field to find average of current and previous year sales) .Then I have to find out the variance in % that how much % sales have increased or decreased in these years.

• ###### 1. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Hi Isa,

TYou have several ways to get that. the easiest is to use Tableau's table calculation. Simply build a view in a worksheet with your average sales measure and your year dimension. Then add a table calculation 'percent difference' or 'year over year growth' on the average sales field. It is the equivalent in a view of this calculated field:

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

Below a double bar chart to represent that:

Workbook attached. Hope this helps!

1 of 1 people found this helpful
• ###### 2. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Thank You so much .Percent difference (quick tab calcuclation) and the calculation field  (ZN(AVG([Sales])) - LOOKUP(ZN(AVG([Sales])), -1)) / ABS(LOOKUP(ZN(AVG([Sales])), -1)) is giving the same result.So I think I can go with Percent difference.Thanks for letting me know the calculation what I had to appply in the calculated field.

• ###### 3. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Hi Stephane,

Can we give user the ability to change the reference band if we have added a reference band to  the above graph such as if they want to c the reference band for 20-30% the reference band automatically changes the way they want.if they want to see -10% to 10 % they can see like that.Can we create parameter ,with which we can give the ability for the user to change the reference band value?

• ###### 4. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Hi Isa,

Yes you can, you simply need to create 2 integer parameters: one for the upper band, the other one for the lower band. Place them on the level of detail of the mark card of the view. You can then select them in your min and max reference band values.

You can then show the parameter control to give the end user the ability to change them!

1 of 1 people found this helpful
• ###### 5. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Can you show that In example worksheet.I could do it ,but  I am unable to display the values in parameters in %.i have to write.01 in the parameter value.So If you could share it it would be of great help.

Again I have one more question..How can we know the % difference  in terms of month.

for example  How can we find the % difference  b.w the average sales of current year  september month compared to average sales of 2015 september.Just want the % differance of current year months to previous year same months?

As with previous question we could able to find the % difference in year .And if we split into month we can see the % differnce along the previous months of the same year,I want the % differnace of same month in 2 different years.How can we achieve that?

• ###### 6. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Hi Sarah,

For the parameters:

and then set up the number default format to percentage (right click default properties> number format on the parameter):

Regarding the percentage difference for years you could add a discrete month dimension in the view:

and edit the table calculation to make it compute the year and month and restart at every year (see top left of the screenshot), this is really clear in the Tableau 10 interface:

Hopes this helps,

1 of 1 people found this helpful
• ###### 7. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Wohhhh.Thanks a lottt Stephane....It worked....

• ###### 8. Re: How to calculate difference or variance in average sales of current year and previous year in tableau?

Hi Stepahane ,

I have another question.

Is there any way to restrict the the number of selections made by user on mutilple dropdown list to any number?

Can we restrict the user to select only 4 or 5 value in a multiple dropdown list?How can we achieve that?