2 Replies Latest reply on Mar 29, 2014 8:43 PM by Steven Barkin

# Tablecalculating row averages, and adjusted row averages. Example excel file attached.

Hello All,

When I set out to do this, I thought this would be straight forward and easy. However I can't seem to get this to work.

The goal is to calculate row averages, for a timeseries of sales, specified by product. I would like the calculation to automatically adjust when I drill down or up on both the date and the product dimension. In addition, I would like to adjust this row average, by only taking the average of specific recurring dimension on the row. For example, only take the average of Q1 over all years of product X. And do this for every Q. This will allow me to compare the results to season adjusted averages.

I have included an excel file, with on the first tab a small data sample (formatted for Tableau), and on the second tab I did what I would like to do in excel.

Is it possible to do this in Tableau?

• ###### 1. Re: Tablecalculating row averages, and adjusted row averages. Example excel file attached.

What I tried so far was to create a calculated value:

SUM([Net Sales EUR])-(WINDOW_AVG(SUM([Net Sales EUR]),FIRST(),LAST()))

And put this on the color mark. However I am unsure if this actually works. Have not found a way to calculate the season adjusted row averages.

• ###### 2. Re: Tablecalculating row averages, and adjusted row averages. Example excel file attached.

Thanks Michel.  I was trying to do almost the same thing and your expression above was helpful.  I am graphing monthly sales over a 1 year period, as well as the seasonal index which I've expressed as follows:

sum([Sales])/sum(Window_Avg(Sum([Sales]), First(), Last())

Question for you - do you think there is a way to calculate this on a calendar year basis, regardless of how many years are displayed on the graph?   In other words, if I am graphing 2010 through 2013 monthly data, I would like each month's sales in 2010 to be divided by the average monthly 2010 sales, and likewise for 2011, 2012, 2013  (different denominators depending on the calendar year).  Any help would be greatly appreciated.