Hi,
I am having the data set as below, the upper table in the picture below. In Tableau, I have 1 dimension - Period, and 3 Measures - Sales, Spend, Traffic. CY is Current Year; LY is Last Year.
I want to achieve a Tableau table in the format of the lower part of the screenshot, where it sums Sales, Spend, Traffic in LY and CY, and have LY Var as (CY-LY)/LY - the percentage change of Current Year (CY) from Last Year (LY).
Any idea?
Right now, I manually created another 3 rows in Excel following LY, LY Var, and calculated the difference. But then in the Tableau table, it will only show the numeric different, not the % difference. If I calculated the % difference in Excel, then LY Var % difference will be summed up, because Sales, Spend, Traffic for LY and CY are summed. LY Var % difference should be averaged, not summed.
Appreciate your help!
The best structure would be Metric, Period, Value as per your example.
However, you can get part way there by using the inbuilt pivot functionality in Tableau - Pivot Data from Columns to Rows
Connect to your data, select your three metric columns, right click and select pivot.
Now in your report create three calculated fields;
CY = sum(if [Period]='CY' then [Pivot Field Values] end)
LY = sum(if [Period]='LY' then [Pivot Field Values] end)
LYChange = ([CY]-[LY])/[LY]
