7 Replies Latest reply on Nov 28, 2016 11:12 AM by D Barnetson

# How to show the % change across the dimension in a table?

Hi,

I am having the data set as below. CY, LY, and LY Var are 3 levels of PERIOD. Initially, there were only CY and LY, and I added in Excel another variable LY Var as the difference between CY and LY, so that we have 3 columns in the table

However, this is still not ideal. I would actually like to show LY Var as the % change between CY and LY, instead of the numeric change. It didn't work if I try to calculate in excel such way, because it should be calculated as the average of % change but Tableau will sum % changes as summing CY and LY.

I tried to create calculated fields for Sales CY Sales LY Sales LY Var, etc. and it worked, but then I won't be able to show all Sales, Spend, and Traffic in the same way - all calculated fields will be laid out in ONE row.

So in one word, my goal is:

1. Have the table as in the picture, but LY Var should be the % change. For example, Sales LY Var should be \$16.8M / Sales CY that is \$16.8M/329.9M ~= 5%/

my concerns are:

1. If data is prepared in CY, LY, and LY Var, I can't have LY Var as % change as it will be added up - which should be treated as average instead. All CY and LY are sum.

2, If I used calculated fields for CY, LY, LY Var for each variable, all of them will be laid out in one row instead of in a 3x3 table.

Can anyone help me figure this out?

Thanks!

• ###### 1. Re: How to show the % change across the dimension in a table?

Hi Chris,

Using the table that you had provided above to create a data set for Tableau I had created a formula to calculate the LY Var as a %:

Place LY Var on the Measure Values Shelf, then click on the drop down arrow of the pill, select 'Format', then covert it to percentage.

See image below:

I hope this helps

Don

• ###### 2. Re: How to show the % change across the dimension in a table?

Sherzod

1 of 1 people found this helpful
• ###### 3. Re: How to show the % change across the dimension in a table?

Hi, thanks for your quick response! I should've been more clear on my data set, but I look forward to your answer!

My data has "Sales", "Spend", Traffic" - 3 measures, "Period" - a dimension, has 3 values CY, LY, LY Var.

An example of data set looks like this (upper table), and the ideal output in Tableau looks like the lower table (also as the Tableau table in my original post):

I am wondering if it's still doable? Thanks again for your help!

• ###### 4. Re: How to show the % change across the dimension in a table?

Hi, thanks for your quick response! I can't open your workbook on my TD 9.2 unfortunately.... Pardon me for my further explanation of the raw data set.

My data has "Sales", "Spend", Traffic" - 3 measures, "Period" - a dimension, has 3 values CY, LY, LY Var.

An example of data set looks like this (upper table), and the ideal output in Tableau looks like the lower table (also as the Tableau table in my original post), sum across the table and average o LY Var % diff:

How can I make this happen... Thanks again for your help!

• ###### 5. Re: How to show the % change across the dimension in a table?

Hi Chris,

You can pivot your data set in Tableau (as long as they are not calculated fields within Tableau).

Here is a link explaining Tableau's Pivot function:

Hope this helps.

Don

• ###### 6. Re: How to show the % change across the dimension in a table?

Hi Don,

Thanks for your response! I tried Pivot Function - pivot Sales, Spend, Traffic, but still didn't know how to have "LY Var" as a percentage. LY Var = CY - LY, and CY, LY are two values for Period.

Right now, I have Sales, Spend, Traffic in Row, and Period in Column. That's how I have the Tableau Table in my original post. I am looking to have LY Var as a % change...

• ###### 7. Re: How to show the % change across the dimension in a table?

Ok. You are almost there.

Now that you have Pivoted the data, You should have 3 columns:

You will need to create 3 mearsures (CY,LY & LY Var)

Place these newly created measures (pills) as per the picture below:

...and there you have it.

Hope this clarifies things a bit better.

Don