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?

    Chris Ma

      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.

       

      question.png

       

      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!