4 Replies Latest reply on Oct 25, 2018 12:15 AM by David Chauncy

# Compare total for this year with average of previous 10 years

Hi

I need to calculate the percentage change of this years Total (_Total_cy) over the average of the previous 10 years.  The attached shows that I have the [_w_average] worked out and the [_Total_cy].

I can't work out how to bring it together, i.e ([_Total_cy]-[_w_Average]/[_w_Average]. In the data the calculation would be (10-16.6)/16.6 = -0.39759.

Thanks David

• ###### 1. Re: Compare total for this year with average of previous 10 years

Hi, David

Please find my solution attached as well as below screenshot as reference.

Hope this helps

ZZ

• ###### 2. Re: Compare total for this year with average of previous 10 years

Replying from phone while waiting at Tableau Conference :-)

total avg :

{avg ( your entire ten year data value)}

avg this year:

{avg (if year (today())= year( your date) then your value end)}

% change:

avg thisyear-total avg/ total avg

Thanks

Deepak

• ###### 3. Re: Compare total for this year with average of previous 10 years

Hi

Thanks so much and the turn around was terrific.  I had another look after I had posted my question and came up with an answer as well which I have attached.

cheers d

• ###### 4. Re: Compare total for this year with average of previous 10 years

Hi

I would like a peer review, ideas on how to present my results in a Dashboard as I don't like what I have done, I find it confusing and so will the business.

I have attached the workbook and a screen print of the dashboard.

Whats the better way to present the above.

The total of years 2008-2017 = 166

The average is the average of years: 2008-2017 is 166/10 = 16.6

And the change is the (10-16.6)/16.6 = -39.75%

You will see in the attached that I have 3 separate workbooks side by side and getting them aligned is painful.

Thanks again for your help.

David