4 Replies Latest reply on Sep 5, 2017 9:19 AM by Jim Dehner

# Year-over-year with a % change

I'm working on automating one of my manual excel reports with Tableau but I can't seem to get the calculations right. Currently, my excel report will take the sum of several different mesaures and calculate a % change based on  that.

Here's that I'm trying to do:

20162017% Change
KPI1

\$100,000

\$97,000-3.00%
KPI2\$35,000\$42,00020.00%
KPI3\$20,000\$21,0005.00%

What I'm after is recreating the % change. The years are being seperated by a Call Date. I've started putting together calculated fields for each KPI but was worried that will get too confusing. Is there a table calculation I can apply to calculate all rows?

• ###### 1. Re: Year-over-year with a % change

Post a sample TWBX with what you currently have, and we can easily show you how to apply a YoY change Table Calculation to the view.  Its difficult to provide help without seeing any data/workbook, but you can use the Sample Superstore datasets that ship with Tableau, or your own randomized subset of data to ask your question here on the Community Forums -- but a workbook will allow volunteers to see/understand your data, and apply the help you need.  At first glance, it seems like a simple Percent Difference table calculation, but its hard to know for sure without seeing a .TWBX file.

• ###### 2. Re: Year-over-year with a % change

You can use an LOD calculation to calculate the columns by year, however I believe the level of detail you are after aggregates the KPI number and the Year together. In this case, your data is not structured optimally, and I would recommend pivoting your data such that you have a field called 'KPI' which would hold the values '1', '2' and '3'. This should also be applied to the date fields (if they are not already pivoted) such that you have a field for 'Date' or 'Year' that holds the values '2016' and '2017' (and all values moving forwards)

Here is a guide to help you with pivoting: Pivot Data from Columns to Rows

Once this is accomplished you can use the following formula:

{FIXED [Year],[KPI] : sum([Sales])}

Which will give you the sales (assumption that these are sales figure) value for each KPI for a given year.

Let me know if this is what you were after or if I've missed the mark!

• ###### 3. Re: Year-over-year with a % change

Hi Tim yes you can do it with a table calculation or your can write your own

The formula is

(LOOKUP(sum([Sales]),0) - LOOKUP(sum([Sales]),-1) )/LOOKUP(sum([Sales]),-1)

or you will get the same if you use the table calc

Either way the results are the same

The Grand Total option will take the formula and apply it to the totals row

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

2 of 2 people found this helpful
• ###### 4. Re: Year-over-year with a % change

Thanks Ti,