Problem with % of column total using calculated dimension

[#1]

Hi all,

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.

Many thanks.

APS

[#2]

Hi All,

no suggestions?

I have the same problem. It's a pivot table with, Country, Partner and sum(sales):

Sales 2010 is calculated with sum(Sales), Sales Land is calculated with sum(TOTAL <LAND> Sales).

The result is the same because Partner is a calculated Dimension (I want to see only the first 8 Partners by sum(Sales)) .

If I take the Partner dimension without calculation (seeing all Partners in the country) the 2nd SUM calculates the total for the land as suggested.

Thanks in advance

Alex

[brenner_martina]

Hi,

this is only an idea, put your calculated dimension in a variable!

[#4]

Hi,

too bad, that doesn't work.

Regards Alex

[#5]

Does no one have an idea?

Regards Alex

[#6]

Why don't you post a test app containing the problem?

I have solved the problem in my app. I created a flag in the load script and then used the flag in a calculated dimension. But if you use the same code in a calculated dimension it doesn't work.

So, in the load script: if (xyz = abc, 1,0) as Flag

Then, in your calculated dimension: if (Flag = 1, then, else) this works

But, if you use this in your CD: if(xyz = abc, then, else) it doesn't work

So, even though you are using the same logic, the flag method works and the other doesn't.

Hopefully this makes sense.