Hi Chris, the attached workbook is one way of doing what you want. I'm sure there are other ways of doing this using table calculations (which I'm still trying to get my head around). Richard will probably come back with something a lot more elegant, but in the meantime this should get you going.
One note: You have enough columns that you're going to hit up against the default setting of 6. Go here:
And then bump up the Row setting (I know, seems counter intuitive to me too):
Hope this is what you're looking for.
Chris.twbx.zip 196.7 KB
Shawn, thanks, this is helpful start. However, I am trying to make the starting and ending quarters dynamic, based on user selection. I've tried using parameters and filters and can't get it right. In other words, if user selects Start Date to be Q3 2009 and End Date to be Q1 2010, then the percent change should be between sales in Q3/09 and Q1/10, while cumulative sales should be all sales in that time period.
I am trying to play with it using parameters, but can't get the column headings to display correctly.
Sorry Chris I miss the parameter/dashboard part. The attached workbook has the parameter controls you wanted. I was a little confused about the difference between My Sales and My Cum Sales. So I gave you a lot of options, here'a a bit of a score card:
Sales: Total of all sales by product without any date limits (notice they don't change with the parameters)
My Sales Selected Quarters: Total sales by product within the date limits (this is probably what you call cumulative)
My Begin Quarter Only: Just what it says
My End Quarter Only: Just what it says
My % Change: Is the percent change from the begin quarter selected to the end quarter selected
Same for Competitor.
Hope this is closer to what you're looking for.
Chris-2.twbx.zip 1.1 MB