Thanks everyone for the help.
Using the pane across solution will give % difference by product sub-category rather than Year over year. So it's slightly different.
The other options give the year over year % difference correctly, which is what we want to see, however, it's not the layout we were looking for.
I noticed that if I change it to Table Down, it does work as is, except it shows results across panes which is not wanted.
I wonder if there is a way I can manually suppress the unwanted results.
1 of 1 people found this helpful
Take a look at sheet 3 in the attached workbook (v10.0). First, I've sorted the years in ascending order:
Then edited the table calc where I compute using Specific Dimensions, and selecting only Year of Order Date:
Sheet 3 screenshot:
Truly hope this gets you closer to the result you are expecting!
Thanks for this suggestion. This method does give correct year over year % difference, however, this solution doesn't have the actual value and % side-by-side which is the desired user experience.
I did find a way to do it. It's not the most elegant solution but it does work.
1. Create the following calc:
(SUM(1) - if ATTR(DATEPART('year', [Order Date])) = 2012
then null else lookup(sum([Number of Records]),1) end)
/if ATTR(DATEPART('year', [Order Date])) = 2012
then null else lookup(sum([Number of Records]),1) end
2. Set it to calculate Table Down.
It works but it's not super pretty. If I put some more time into it I could probably find a way to avoid hard coding the year = 2012 but you get the idea.