Problem with % of column total using calculated dimension
I have an object that calculates sales by product as a % of total weeky sales (as shown below).
(click image to enlarge)
My dimension (Week) is actually a calculated dimension that I use to overcome the problem of when week 1 or 52/3 covers more than one year e.g. 2010 week 52 starts on 27 Dec 2010 and finishes on 02 Jan 2011.
Normally, I would simply use sum(sales) / sum(total <[Week]> sales) to calculate % of weekly sales for each product. But, because it's a calculated dimension, this won't work. I've tried using the calculated dimension's label (called [Calculated Week] ) i.e. sum(total <[Calculated Week]> sales) but the label isn't recognised so this doesn't work. I've also tried pasting in the calculated dimension's expression but this doesn't work either.
Does anyone have a solution?
If necessary, I can post a test app with the object in which may make more sense.