If the data set is exactly as shown, you could create calculated fields like - SALES2010:
if [period]=2010 then [sales] end
and then with other calc fields do a (sum([Sales2010]) - sum([Sales2009]))*[TotalProfit2009]
Alex, thanks for pointing me in the direction to use calculated fields to store aggregate values. I'm still struggling how I can combine the values of different years in one table though.
In the attached workbook I followed your suggestions and created the fields for sales2009 and sales 2010. For the calculation I need to multiply their difference by the Total Profit value for 2009 (at company level) for each line. If I just create a TotalProfit2009 field by dividing profit by sales it will hold the 2009 profit margin for each product. I guess I could use a WINDOW_SUM but somehow I can't get it work (when I place [Period] on the Level of Detail shelf the other values disappear...).
This is a classic example of an issue I go on about occasionally - which is that there is really a missing addressing function in table calculations. As well as FIRST(), LAST() and INDEX() you really need a THIS() to refer to the second context you talk about. This comes up a lot when you are trying to do correlation calculations.
I know the Tableau folk considered having a THIS() function when they were first developing table calculations but opted not to because of adding complexity and the risk that it would make it too easy to generate really slow calculations (which seems a bit ironic seeing as the WINDOW_XXX() functions get really slow anyway for exactly the reason they were worried about - unless you add a lot of complexity to work around the issue).
The only ways I have ever found of getting around the limitation are either hard-coding specific offsets in the way Alex is suggesting if the number of dimensions is manageable or by doing the correlation in SQL in the data source.
This old thread of Alex's discusses the issue a bit more.
I don't feel totally clear on what you're doing, but it seems like you need to avoid using WINDOW_SUM() and use TOTAL() instead, and use different partitioning of the nested table calcs that make up the variance measures.
I created a TOTAL(SUM([Profit 2009)) measure called T Profit 2009. Then I used that and your existing Sales Variance measure that's [Sales 2009] to create the following "Profit variance due to volume (nested using total)" calc:
[Sales Variance]*[T Profit 2009]
Then after dragging that into the view I set the Compute Using for Sales Variance to be Period and for T Profit 2009 to be Table (Down). This measure has really big numbers (should that * really be a /?), so I set the number format to be Billions.
Setting the compute using for the T Profit 2009 measure to Period fails, I'm not sure why, I think it also has to do with why trying to calculate the profit using WINDOW_SUM() fails. There are two periods in the underlying data and the [Profit 2009] field is only returning rows for 1/2 of them, so when the Period is 2010 the total profit measures are returning either an empty value or 0 or Null (you can see this on the WS Profit 2009 along Table (Down) measure in the "3rd try - jtd" worksheet) and then the Profit variance is returning something like an empty value, Null, or 0 for the 2010 values of Period and then another value for 2009.
Does this help, or am I totally offbase here?