Create a Calculated Dimension in Pivot Table?

[#1]

I have three dimensions. 'Product','ArticleNo','Week' and an expression 'Units Sold'. The table is pivoted on 'Week' dimension with 'Units Sold' is its measure of expression. Now I need to add average as a Calculated Dimension, which should calculate overall average of 'Units Sold'. Anyone, how can I do this?

[michael_maeuser · Accepted solution]

aggr(avg(unitssold), product, article, week)

[#3]

Hi,

Sum(unitssold)/Sum({<product=, article=, week=>}unitssold). Try this I hope it helps you

Thanks and Regards,

S.Amuthabharathi

[#4]

Hi,

What dimensions do you want to calculate the average over? You can use the TOTAL modifier to ignore dimensions in the chart for example:

Average(TOTAL<Product, ArticleNo>Units Sold) will give you the average Units Sold calculated over the Product and Article dimensions only, ignoring the Week Dimension.

Also just noticed in your original post you mentioned calculated dimension. Are you sure you want to calculate a dimension in this case or are you wanting to create an expression to calculate the average over a specific set of dimensions?

Andy

[#5]

what if i want average for, lets say, week 1 and 2. How do I do that, using below expression?

aggr(avg(unitssold), product, article, week)

[#6]

I'm not too sure how you would do that for specific weeks without creating a multitude of expressions. Although in a pivot table you can refer to the adjacent cell using the "Last" function. Look it up in the chart inter record functions in help.

The only thing I can thing of with the expression that you have would be to use something like

aggr(avg(unitssold), product, article, month)

This would then calculate the average for the month that those weeks belonged too.

Would that work for you?

Andy