How do I overide a chart dimension within the same chart expression?

[bobnewbie]

Hi All, please can you assist:

I have a chart with 2 dimensions, Product and Year and a single expression sum(Sales). I would like to create another expression that shows the percentage:

total sales of product  (but only for that dimension year) / total sales of all products (but only for that dimension year as well)

Example:

Product            Year        Sales             %

Apple               2015          5              5 / (5+7+9)

Apple               2016          6              6 / (6+8)

Pear                2015           7

Pear                2016           8

Banana           2015           9

Thanks all.

[arulsettu]

sum(sales)/sum(TOTAL sales)

[kumarkp412]

Hi Bob,

For secound expression use the aggr function with total of sales by year.

try this;

ex: sum(sales)/aggr(sum(sales),year)

[settu_periasamy]

May be try

=sum (Sales)/sum (TOTAL <Year> Sales)

[jonathandienst · Accepted solution]

settu_periasamy has the correct answer for general use:

=sum (Sales)/sum (TOTAL <Year> Sales)

You should avoid using Aggr() for expressions like this (aggr() can be a performance killer and should be used sparingly).

Use set analysis if you want to limit to the current year only

[bobnewbie]

Many thanks all. Thanks Jonathan, the team and I have learned something new and very useful.